[SOLVED] populate drop down box from database table of values

[bradkenyon]

I have a table (venues) that has 21 different items.

 

Then I have a table (volunteers_2009) that I store volunteer info into, for a festival.

 

Each volunteer will be assigned a venue, so volunteers_2009.venue_id is venues.id

 

I want to display each volunteer, with the select drop down box pre-selected to the venue they are assigned.

 

Here is the query statement that allows me to display the venue that is assigned for each volunteer, but I need to populate a select drop down box with all the venues (venues.venue_name) from the table venues.

 

$query = "SELECT volunteers_2009.id, volunteers_2009.comments, volunteers_2009.choice1, volunteers_2009.choice2, volunteers_2009.choice3, volunteers_2009.lname, volunteers_2009.fname, volunteers_2009.venue_id, venues.venue_name FROM volunteers_2009 AS volunteers_2009 LEFT OUTER JOIN venues ON (volunteers_2009.venue_id = venues.id) ORDER by $order $sort";

 

table: volunteers_2009 columns: id, lname, fname, comments, interest, choice1, choice2, choice3, venue_id

 

table: venues columns: id, venue_name

 

How do I go about populating the select drop down box with all the venues within the venues table, and pre-select the venue that volunteer (volunteers_2009.venue_id == venues.id) is assigned to?

 

Example: volunteer (volunteers_2009.id) 7 has venue_id (volunteers_2009.venue_id, venues.id) 4

<form action="upd.php?id=7">
<select name="venue_id">
    <option value="1">Bagpipe Competition</option>
    <option value="2">Band Assistance</option>
    <option value="3">Beer/Wine Pouring</option>
    <option value="4" selected>Brochure Distribution</option>
    <option value="5">Childrens Area</option>
    <option value="6">Cleanup</option>
    <option value="7">Cultural Center Display</option>
    <option value="8">Festival Merch</option>
</select>
<input type="submit" value="submit" name="submit">
</form>

 

 

I appreciate any help, thank you.

 

[Smoke718]

Try something like this.

<?php
$venueid = $_POST['venue_id'];
$query = mysql_query("SELECT * FROM volunteers_2009"); // your query here, modify it however you'd like
$html = 'select name="venue_id">';
while($data = mysql_fetch_array($query)) //start looping through the results
{
    extract($data); //$data is an array containin all the datafrom the row, we use extract() to bring them out of the array into variables 
    if($venueid == $venue_id) //if the id from the DB matches the one previously selected
    {
        $html .= "<option value='$venue_id' selected>$venue_name</option>"; //auto select it
    }
    else
    {
        $html .= "<option value='$venue_id'>$venue_name</option>"; //do not select it
    }
}
$html .= "</select>";
?>
<form action="upd.php?id=7">
<?php echo $html; ?>
<input type="submit" value="submit" name="submit">
</form>