multiple drop down menus with mysql and php

[taylortay]

I have scoured the web for the answer to this question. I am a nubie to PHP so the answer may be simple I just don’t see it.
I’m trying to dynamically create two drop down menus from the results of one query. Sounds simple enough but I want to have my php execute most of the work above and then just plug in my values into my html below. Here is my example so far.

<?php
$form_query = "SELECT `class_name`, `instructor` FROM `schedule`";

$form_fetch = mysql_query($form_query);

while ($result = mysql_fetch_array($form_fetch)) {
    $class = $result[0];
    $class_options = "<option value='$class'>" . $class . " </option>";
    $instructor = $result[1];
    $instructor_options = "<option value='$instructor'>" . $instructor . " </option>";

}
?>
<h1>Add New Class or Workshop</h1>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post">

Select Class Name: 
<select name="instructor">
    <option>Select Class Name</option>
    <?php echo $class_options;?>
</select>

Select Instructor Name: 
<select name="instructor">
    <option>Select Instructor Name</option>
    <?php echo $instructor_options;?>
</select>

</form>

This is only giving me the last result from each column. How can I get all of the results form the column class name into the drop down for the Class Names and all the results from the column instructors into the Instructors drop down menu?

Any help would be greatly appreciated :)

[veedeoo]

Hi,

Before jumping into possible solution, may I have more info. about your database? class and the instructor tables.

The reason I am asking is because of this

$class = $result[0];

and this

 $instructor = $result[1];

Both will return ONE..

[taylortay]

I have solved my own problem and this is how I did it.

$classes = array();
$instructors = array();

while($row = mysql_fetch_assoc($form_fetch)){
    $classes[] = $row['class_name'];
    $instructors[] = $row['instructor'];
}

?>
<h1>Add New Class or Workshop</h1>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post">

Select Class Name:
<select name="instructor">
    <option>Select Class Name</option>
    <?php
                    foreach ($classes as $class) {
                        echo "<option value='$class'>" . $class . " </option>";
                    }
                ?>
</select>

Select Instructor Name:
<select name="instructor">
    <option>Select Instructor Name</option>
    <?php
                    foreach ($instructors as $instructor) {
                        echo "<option value='$instructor'>" . $instructor . " </option>";
                    }
                ?>
</select>

</form>

I made seperate arrays from eacch of the columns!

It works perfectly.

Thank you!

[veedeoo]

ok, I think I got it...

Change this

$class = $result[0];

to

$class = $result['class_name'];

and

$instructor = $result[1];

to

 $instructor = $result['instructor'];

[taylortay]

Yes, but I also defined it as an array as well here:

$classes = array();

and then populated that array with the results from the while loop

$classes[] = $row['class_name'];

That way I could loop through the $classes array using the foreach to create my drop down optoins ;)

Are you sure that this is the best way to do it?
WIll all instructors be in the table? Will all classes? Will there be duplicates?
I imagine there has to be as you can only add instructors or classes that already exist.

Usually there will be related class and instructor tables, helping to normalize the DB. Creating related fields may add a layer of complexity when producing sql, but it's worth it.

Or have I totally misunderstood the nature of the table?

[taylortay]

Im not 100% sure its the best way it was how I got it to work. The informaton is coming from a database so the values are already defined. I should run some kind of check to make sure no duplicates show up.