Populate select drop down from a database table

Question

I have a table ("venues") that stores all the possible venues a volunteer can work, each volunteer is assigned to work one venue each.

I want to create a select drop down from the venues table.

Right now I can display the venue each volunteer is assigned, but I want it to display the drop down box, with the venue already selected in the list.

<form action="upd.php?id=7">
<select name="venue_id">
<?php //some sort of loop goes here
print '<option value="'.$row['venue_id'].'">'.$row['venue_name'].'</option>';
//end loop here ?>
</select>
<input type="submit" value="submit" name="submit">
</form>

For example, volunteer with the id of 7, is assigned to venue_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>

Brochure Distribution option will already be selected when it displays the drop down list, because in the volunteers_2009 table, column venue_id is 4.

I know it will take a form of a for or while loop to pull the list of venues from the venues table

My query is:

$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";

How do I populate the select drop down box with the venues (volunteers_2009.venue_id, venues.id) from the venues table and have it pre-select the venue in the list?

Answer 1

$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";

$res = mysql_query($query);
echo "<select name = 'venue'>";
while (($row = mysql_fetch_row($res)) != null)
{
    echo "<option value = '{$row['venue_id']}'";
    if ($selected_venue_id == $row['venue_id'])
        echo "selected = 'selected'";
    echo ">{$row['venue_name']}</option>";
}
echo "</select>";

:)

Answer 2

assuming you have an array of venues...personally i don't like to mix the sql with other wizardry.

function displayDropDown($items, $name, $label, $default='') {
  if (count($items)) {
    echo '<select name="' . $name . '">';
    echo '<option value="">' . $label . '</option>';
    echo '<option value="">----------</option>';
    foreach($items as $item) {
      $selected = ($item['id'] == $default) ? ' selected="selected" : '';
      echo <option value="' . $item['id'] . '"' . $selected . '>' . $item['name'] . '</option>';
    }
    echo '</select>';
  } else {
    echo 'There are no venues';
  }
}

Answer 3

Hello

Great answer to a question very similar to mine. I wonder if someone could help?

I need the values of the inputs to be populated by the database. My code works great for all text and textarea inputs but I can't figure out how to assign the database value to the drop down lists eg. 'Type of property' below. It revolves around getting the 'option selected' to represent the value held in the database.

Here is my code:

$result = $db->sql_query("SELECT * FROM ".$prefix."_users WHERE userid='$userid'"); $row = $db->sql_fetchrow($result);

     echo "<center><font class=\"title\">"._CHANGE_MY_INFORMATION."</font></center><br>\n";
     echo "<center>".All." ".fields." ".must." ".be." ".filled."

  <form name=\"EditMyInfoForm\" method=\"POST\" action=\"users.php\" enctype=\"multipart/form-data\">

           <table align=\"center\" border=\"0\" width=\"720\" id=\"table1\" cellpadding=\"2\" bordercolor=\"#C0C0C0\">

 <tr>
  <td align=\"right\">".Telephone." :</td>
  <td><input type=\"text\" name=\"telephone\" size=\"27\" value=\"$row[telephone]\"> Inc. dialing codes</td>
 </tr>
 <tr>
  <td align=\"right\">".Type." ".of." ".property." ".required." :</td>
  <td>Select from list: <select name=\"req_type\" value=\"$row[req_type]\">

  <option>House</option>
  <option>Bungalow</option>
  <option>Flat/Apartment</option>
  <option>Studio</option>
  <option>Villa</option>
  <option>Any</option>

  </select></td>
 </tr>

 

Answer 4

Nigel,

See the answer with the check mark next to it. It go me start in solving my problem. First I select my data from the table, then I select the data i want display in my dropdown menu. If the data from the primary table matches the data from the dropdown table selected is echoed.

<?php $query9 = "SELECT *
           FROM vehicles
           WHERE VID = '".$VID."'
           ";
    $result9=mysql_query($query9);
    while($row9 = mysql_fetch_array($result9)){
        $vdate=$row9['DateBid'];
        $vmodelid=$row9['ModelID'];
        $vMileage=$row9['Mileage'];
        $vHighBid=$row9['HighBid'];
        $vPurchased=$row9['Purchased'];
        $vDamage=$row9['Damage'];
        $vNotes=$row9['Notes'];
        $vKBBH=$row9['KBBH'];
        $vKBBM=$row9['KBBM'];
        $vKBBL=$row9['KBBL'];
        $vKBBR=$row9['KBBR'];
        $vYID=$row9['YID'];
        $vMID=$row9['MID'];
        $vModelID=$row9['ModelID'];
        $vELID=$row9['ELID'];
        $vECID=$row9['ECID'];
        $vEFID=$row9['EFID'];
        $vColorID=$row9['ColorID'];
        $vRID=$row9['RID'];
        $vFID=$row9['FID'];
        $vDID=$row9['DID'];
        $vTID=$row9['TID'];
    }?>

<?php $query1 = "SELECT *
                           FROM year
                           ORDER BY Year ASC
                           ";
                $result1=mysql_query($query1);
                echo "<select  name='Year'>";
                while($row1 = mysql_fetch_array($result1)){
                echo "<option value = '{$row1['YID']}'";
                if ($vYID == $row1['YID'])
                    echo "selected = 'selected'";
                echo ">{$row1['Year']}</option>";
            }
            echo "</select>";
        ?>