How do I populate the selected value of drop down lists with Mysql data?

Question

Hello

I need the values of form inputs to be populated by the sql 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 1

using your current code:

<?php
  $options = array('House', 'Bungalow', 'Flat/Apartment', 'Studio', 'Villa', 'Any');

  foreach($options as $option) {
    if ($option == $row['req_type']) {
      print '<option selected="selected">'.$option.'</option>'."\n";
    } else {
      print '<option>'.$option.'</option>'."\n";
    }
  }
?>

assuming $row['req_type'] is one of the values in $options. i strongly suggest quoting your array elements (ie $row['req_type'] instead of $row[req_type]. the latter method generates errors under error_reporting(E_ALL)

you also might want to look at resources such as phpbuilder.com. some articles are pretty outdated, but will provide you with some basics on how to better structure your code. (for example, separating your HTML from your PHP code woulud help readiability in this sitaution a lot).

edit: as per the comments, any information displayed should be escaped properly (see htmlentities() for example).

Answer 2

Here is another way of going about it.

<?php
$query = "SELECT * FROM ".$prefix."_users WHERE userid='$userid'";
$result = mysql_query($query); ?>
$options = array('House', 'Bungalow', 'Flat/Apartment', 'Studio', 'Villa', 'Any');
<select name="venue">
<?php
    while($row = mysql_fetch_array($result)) {
     print '<option value="'.$option.'"';
     if($row['req_type'] == $option) {
      print 'selected';
     }
     print '>'.$option.'</option>';
    } 
?>
</select>

Answer 3

May not be efficient, but it's simple & gets the job done.

$dropdown = str_replace("<option value=\"".$row[column]."\">","<option value=\"".$row[column]."\" selected>",$dropdown);

Answer 4

Thanks Owen. Your code seems perfect but gave lots of php errors. I simplified it to this but just get whitespace as options:

<td>Select from list: <select name=\"req_type\">     

$option = array('House', 'Bungalow', 'Flat/Apartment', 'Studio', 'Villa', 'Any');

foreach($option as $option) {  
if ($option == $row[req_type]) {    
    <option selected>$option</option>} 
else {    
<option>$option</option>}};
</select></td>

Answer 5

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

            // options defined here
            $options = array('House', 'Bungalow', 'Flat/Apartment', 'Studio', 'Villa', 'Any');

       ?>     
            <center><font class="title"><?php echo _CHANGE_MY_INFORMATION; ?></font></center><br />

            <center> All fields must be filled </center> 
            <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="<?php echo htmlentities($row['telephone']); ?>" /> Inc. dialing codes
                  </td>        
                </tr>        
                <tr>                
                  <td align="right">Type of property required :</td>                                          
                  <td>Select from list:
                    <select name="req_type">
                  <?php foreach($options as $option): ?>     
                      <option value="<?php echo $option; ?>" <?php if($row['req_type'] == $option) echo 'selected="selected"'; ?>><?php echo $option; ?></option>                  
                  <?php endforeach; ?>
                     </select>  
                   </td>          
                  </tr>

...

oops.. a little too carried away.

Answer 6

previously covered: link text

Answer 7

Thanks everyone for all your help. Although I couldn't get any one answer to work it gave me ideas and I have accomplished the job! I ended up going about it a long-winded way but hey-ho if it's not broke don't fix it.

if ($row[$req_type]=='House'){
    $sel_req_type1='selected';
}
else if ($row[$req_type]=='Bugalow'){
    $sel_req_type2='selected';
}

etc. etc. etc.

And in the table:

<option $sel_req_type1>House</option>
<option $sel_req_type2>Bulgalow</option>

etc. etc. etc.

I still have other issues to overcome i.e. htmlentities for the text fields and quoting the array elements (ie $row['req_type'] instead of $row[req_type]. But I'll ask that on another question. Thanks again

Answer 8

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.

 $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'];
}

$query1 = "SELECT * FROM year ORDER BY Year ASC "; $result1=mysql_query($query1); echo ""; while($row1 = mysql_fetch_array($result1)){ echo "{$row1['Year']}"; } echo "";