[RESOLVED] Using multiple WHILE loops for drop down boxes

Fawkman

Hi Guys

I have two drop down boxes on a form, each one calls in fields from different tables in my db. I call these fields in dynamically, using the following code:

//user a dropdown to select all the modules
$query = "SELECT module_id, name FROM module";
$data = mysqli_query($dbc, $query);

$options="";

while ($row=mysqli_fetch_array($data)) {
    $module_id=$row["module_id"];
	$name=$row["name"];
	//by posting the subject_name into the module table, this code will insert the actual subject_id into the fk subject_id field
	$options.="<option value= \"$module_id\">".$name."</option>";
}

//user dropdown for student
$query = "SELECT student_id, first_name, surname FROM student";
$data = mysqli_query($dbc, $query);

$options="";

while ($row=mysqli_fetch_array($data)) {
    $student_id=$row["student_id"];
	$first_name=$row["first_name"];
	$surname=$row["surname"];
	//by posting the subject_name into the module table, this code will insert the actual subject_id into the fk subject_id field
	$options.="<option value= \"$student_id\">".$first_name." ".$surname."</option>";

}

So one drop down should show a list of modules and one drop down should show a list of students.
When i run this code I don't get an error, but both drop down boxes are populated with the same Student table attributes. Is this because I am using two WHILE loops and it takes the values of the latest one? Could anyone help me with how I get the drop down boxes to display the appropriate attributes from the two tables?

I then want to insert the selected fields into another table, but I know how to do this, just not sure how to populate the seperate drop downs.

Thanks a lot for your time. Really appreciate it.

halojoy

Because after the first part, you reset $options string to empty.

You can call first $options1. You can call second $options2.

//user a dropdown to select all the modules
$query = "SELECT module_id, name FROM module";
$data = mysqli_query($dbc, $query);

$options1 = "";
while ($row=mysqli_fetch_array($data)) {
    $module_id=$row["module_id"];
    $name=$row["name"];
    //by posting the subject_name into the module table, this code will insert the actual subject_id into the fk subject_id field
    $options1 .= "<option value= \"$module_id\">".$name."</option>";
}

//user dropdown for student
$query = "SELECT student_id, first_name, surname FROM student";
$data = mysqli_query($dbc, $query);

$options2 = "";
while ($row=mysqli_fetch_array($data)) {
    $student_id=$row["student_id"];
    $first_name=$row["first_name"];
    $surname=$row["surname"];
    //by posting the subject_name into the module table, this code will insert the actual subject_id into the fk subject_id field
    $options2 .= "<option value= \"$student_id\">".$first_name." ".$surname."</option>";

} 

echo $options1;
echo '<br />';
echo $options2;

?>

Fawkman

Thanks very much for your reply. Thats great. I have included the username with the Student Name also, that way it doens matter if there are two Gemma Smiths.

However the part I thought I had sorted is'nt working how I thought. Could I ask you to see if you have any thoughts?

$name = mysqli_real_escape_string($dbc, trim($_POST['name']));
// the student name
$first_name = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
$surname = mysqli_real_escape_string($dbc, trim($_POST['surname']));
$username = mysqli_real_escape_string($dbc, trim($_POST['username'])); 


$query = "INSERT INTO module_student (module_id, student_id) VALUES ('$name','$first_name')";
        mysqli_query($dbc, $query) or die(mysqli_error($dbc) . "<br /> \n $query"); //or die ('ERROR ERROR ERROR');

When I click submit on the form, the student_id, is saved into the module_id field in the db and nothing is saved to the student_id field? Can anyone shed some light on this for me, I can't find what the issue is!

Thanks for your help

halojoy

Hard to say.
It could be from your select.
the important is the <SELECT name="xxxxx"> attribute
WHERE we read later the $_POST['xxxxx']

In your script I should make a DEBUG output of the $POST array
to see what values are really being submitted

echo '<pre>';  //Display adjustment
print_r($_POST); //DEBUGGING array $_POST

$name = mysqli_real_escape_string($dbc, trim($_POST['name']));
// the student name
$first_name = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
$surname = mysqli_real_escape_string($dbc, trim($_POST['surname']));
$username = mysqli_real_escape_string($dbc, trim($_POST['username']));

Fawkman

Thats really usefull, I didnt know you could do that. A friend of mine who develops in PHP, recommended i read the PHP manual, but I havent started yet. Do you have any other debugging secrets?

I fixed this issue, It was the naming of the option value for the Student Name. I had it set to NAME, which was what the Module Name option value was set to.

Thanks a lot for your help, as always, very much appreciated.

halojoy

Not many secrets.
But is always helpful to make a debug printout of variable values in the script.

And of course use error_reporting(E_ALL) + display_errors while developing.
After the script is working good we can remove such debugging.
Or comment them out.

<?php

//   Uncomment following to start debug reporting
//error_reporting(E_ALL); // turn on full reports
//ini_set( 'display_errors', '1' ); //make sure reports displayed in page


// the rest of your script here

?>

bradgrafelman

Also note that changing error_reporting and/or display_errors on-the-fly at the top of your script won't be useful if you have parse errors.

That's one reason why it's much better to develop locally or on a development server (not the live site) where you can setup your php.ini such that it's debug-friendly (hence why there are two versions of php.ini packed in to the .zip file - one for production, one for development).

Fawkman

Thats great, thanks for the debgu info, very useful.

have a nice weekend and thanks for the help