PDO dropdown boxes post

MySQL database
#1

I am trying to get a dropdown box to choose what is printed out.
The dropdown is populated by course names and when a selection is made I want certain course modules releated to that course printed.
Heres the dropdown box code:

[code]

<?php $stmt = $db->prepare('Select Course_Name from coursename'); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo ''.$row['Course_Name'].''; } ?>

[/code]

And heres another section of code that just prints out course module/code that I thought might make it easier to understand my question.

[code]<?php
require_once(‘connect.php’);
$sql = “SELECT CourseID, Module_Name,ModuleID, Module_Code, Module_Year
FROM coursemodule ORDER BY ModuleID ASC”;
$stmt = $db->prepare($sql);
$stmt->bindParam(’:ModuleID’, $CourseID, PDO::PARAM_INT);
$stmt->execute();
while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
echo “

” . $output->Module_Name . " " . $output->Module_Code . “

\n”;

}
?> [/code]
Can anyone advise me as im lost.

#2

You are missing a few concepts on all of this.
[php]

<?php $stmt = $db->prepare('Select Course_Name from coursename'); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo ''.$row['Course_Name'].''; } ?>

[/php]

You would pull an ID from the table and use that to get further information later.

[php] <?php
require_once(‘connect.php’);
$sql = “SELECT CourseID, Module_Name,ModuleID, Module_Code, Module_Year
FROM coursemodule ORDER BY ModuleID ASC”;
$stmt = $db->prepare($sql);
$stmt->bindParam(’:ModuleID’, $CourseID, PDO::PARAM_INT);
$stmt->execute();
while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
echo “

” . $output->Module_Name . " " . $output->Module_Code . “

\n”;

}[/php]

You are using prepared statements, which is good, but where exactly is your placeholder in the query? And your initial query is pointless, because you are not using what was selected in the new query above.

#3

I currently have two tables in a database working separately; one table allows the user to select a course from a drop down box and the other table is displaying course modules. I am trying to make the selection of course name effect the course modules that are printed out.
Heres my code so far. Course name dropdown box-

[code]

<?php $stmt = $db->prepare('Select Course_Name, CourseID from coursename'); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo ''.$row['Course_Name'].''; } ?>

[/code]
Module print out-

[code]<?php
require_once(‘connect.php’);
$sql = “SELECT CourseID, Module_Name,ModuleID, Module_Code, Module_Year FROM coursemodule ORDER BY ModuleID ASC”;
$stmt = $db->prepare($sql);
$stmt->bindParam(’:ModuleID’, $CourseID, PDO::PARAM_INT);
$stmt->execute();
while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
echo “

” . $output->Module_Name . " " . $output->Module_Code . “

\n”;

}
?> [/code]
I have tried but seem to be stuck; any insight to get them working together would really be apreciated.
Thanks in advance

#4

Currently, there is no way to identify what was selected.

[php]echo ‘<option name=“courseID” value"{$row[‘CourseID’]}">{$row[‘Course_Name’]}’;[/php]

The above piece will return the CourseID if passed with a form using the $_POST variable courseID.

#5

So I need to $_POST the selection of the dropdown box?

#6

You should be using a form to pass the data. You may also use an AJAX call, but the handling is the same.

EXAMPLE:
[php]<?php
if ( isset( $_POST[‘send’]))
{
print_r( $_POST );
}
?>

<?php 
  $stmt = $db->prepare('Select Course_Name, CourseID from coursename');
  $stmt->execute();
     while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo '<option>'.$row['Course_Name'].'</option>';
     }
  ?>
[/php]
#7

Oh ok thanks for the info. How do I go about then displaying the related modules with the courseID selection from the dropdown (courseID)? Hope that question makes sense.

#8

Currently displaying course name when the selection is sent instead of the modules?

#9

What is the current code? I have a feeling you are using the prepared statements incorrectly, and possibly the SQL statements themselves are incorrect for what you are looking for,

#10

I ran the example code you provided along with [code]<?php
require_once(‘connect.php’);
$sql = “SELECT CourseID, Module_Name,ModuleID, Module_Code, Module_Year FROM coursemodule ORDER BY ModuleID ASC”;
$stmt = $db->prepare($sql);
$stmt->bindParam(’:ModuleID’, $CourseID, PDO::PARAM_INT);
$stmt->execute();
while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
echo “

” . $output->Module_Name . " " . $output->Module_Code . “

\n”; 
}

?>[/code]
I have tried altering your example but i can’t get my head around what is required.
Any tips on what im missing/ doing wrong?

#11

Still trying and now recieving errors Undefined variable: result and Invalid argument supplied for foreach on the same line.
Is my latest attempts heading in the right direction?

[code]

<? try { $sql = "SELECT * FROM coursemodule WHERE CourseID=?"; $stmt = $pdo->prepare($sql); $stmt->execute(array( $_POST['CourseID'] )); $result = $stmt->fetchAll(); ?>
<?php foreach ($result as $row) { echo <<<EOT EOT; 
    } // End Foreach

?>
{$row['moduleID']} {$row['courseID']} {$row['module_name']} {$row['module_code']}
[/code]
#12

Don’t use short tags for your php. Even if it is supported, it is a bad practice.

Do a print_r on the result to see if it holds an array.

I assume the try has a matching catch somewhere farther down?

#13

Didn’t mean to use short tags so I have that sorted now.
I will try to do a print_r on results now and I don’t have a catch so good spot.
Is this what you mean in terms of a catch?

[code]
} catch (try $e) {
echo 'Caught try: ', $e->getMessage(), “\n”;
}

// Continue execution
echo ‘Hello World’;
?>[/code]
Once again I really apreciate the help.

#14

Ok so when I select the dropdown box and send it I retrieve an array back containing the selection made.
e.g. Array ( [Course_Name] => BSc Hons Comp (Internet Systems) [send] => Send )

I’m currently trying to change it so I retrieve related info to the selection (course modules).

[code]<?php
require_once(‘connect.php’);
$sql = “SELECT CourseID, Module_Name,ModuleID, Module_Code, Module_Year FROM coursemodule ORDER BY ModuleID ASC”;
$stmt = $db->prepare($sql);
$stmt->bindParam(’:ModuleID’, $CourseID, PDO::PARAM_INT);
$stmt->execute();
while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
echo “

” . $output->Module_Name . " " . $output->Module_Code . “

\n”;

}
?>

<?php if ( isset( $_POST['send'])) { print_r( $_POST ); } ?> <?php 
   $stmt = $db->prepare('Select Course_Name, CourseID from coursename');
   $stmt->execute();

      while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   echo '<option>'.$row['Course_Name'].'</option>';
      }
	    $sql2 = "SELECT CourseID, Module_Name,ModuleID, Module_Code, Module_Year  FROM coursemodule ORDER BY ModuleID ASC";
$stmt = $db->prepare($sql);
$stmt->bindParam(':ModuleID', $CourseID, PDO::PARAM_INT);
$stmt->execute();
while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
   echo "<p>" . $output->Module_Name  . " " . $output->Module_Code  . "</p>\n";

}

   ?>
[/code] I'm aware that at the top of my code I print out the course modules that was me making sure the data can be reached. I seem to be going around in circles in my attempts so could desperately do with some assistance.
#15

Try something more along these lines,

[php]

<?php require_once('connect.php'); if ( isset( $_POST['send'])) { $sql = "SELECT Module_Name,ModuleID, Module_Code, Module_Year FROM coursemodule WHERE CourseID = :courseId ORDER BY ModuleID ASC"; $stmt = $db->prepare($sql); $stmt->bindParam(':courseId ', $_POST['Course_Name'], PDO::PARAM_INT); $stmt->execute(); while ($output = $stmt->fetch(PDO::FETCH_OBJ)) { echo "

" . $output->Module_Name . " " . $output->Module_Code . "

\n"; } } ?>

[/php]

From the look of the query, you have some issues with your database design as well.

#16

Getting the error
“Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: parameter was not defined’ in N:\comparison.php:62 Stack trace: #0 N:\t\comparison.php(62): PDOStatement->bindParam(’:CourseId ', ‘BSc Hons Comp (…’, 1) #1 {main} thrown in N:\comparison.php on line 62”
Thats Line 11 shown in your post for reference; Thought it was because CourseID wasn’t in the SELECT but the same error is showing.
Will continue to try in the meantime,

#17

Place this $_POST[‘Course_Name’] right after the if isset setup. See if there is a value passed, it looks like you skipped over adding the values to the options selection.

#18

The same error shows up if I do that.

#19

Copy paste the code as it is now. Easier to answer when getting updates so we can see what you’re trying. If not then we and you can quickly get “out of sync” with what code we’re actually looking at :slight_smile:

#20

No problem.

[code] <?php
require_once(‘connect.php’);

if ( isset( $_POST[‘send’]))

{

    $sql = "SELECT Module_Name,ModuleID, Module_Code, Module_Year  FROM coursemodule WHERE CourseID = :courseId ORDER BY ModuleID ASC";
   $stmt = $db->prepare($sql);
   $stmt->bindParam(':courseId ', $_POST['Course_Name'], PDO::PARAM_INT);
   $stmt->execute();
   while ($output = $stmt->fetch(PDO::FETCH_OBJ)) {
      echo "<p>" . $output->Module_Name  . " " . $output->Module_Code  . "</p>\n";




 
 }

}

?>

<?php 
   $stmt = $db->prepare('Select Course_Name, CourseID from coursename');
   $stmt->execute();

      while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   echo '<option>'.$row['Course_Name'].'</option>';
      }
	 

   ?>
[/code]
Sponsor our Newsletter | Privacy Policy | Terms of Service