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

Question

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>
....

Owen · Answer 1 · 2008-10-16T01:36:19.190

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).

definitely, although looking at the code the OP provided it seems there's a lot of stuff to be improved on — Owen, Oct 15 '08 at 18:55

score 1 · Answer 2 · edited Aug 26 '11 at 05:40

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

score 0 · Answer 3 · answered Oct 15 '08 at 22:45

0

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);

answered Oct 15 '08 at 22:45

user24632

157
4
8

score 0 · Answer 4 · edited Oct 16 '08 at 01:40

0

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>

edited Oct 16 '08 at 01:40

Owen

82,995
21
120
115

answered Oct 16 '08 at 01:25

i've gone over my code and corrected an error i found, please compare the result. please note you can't mix HTML/PHP, if you are in PHP, you must be within tags (i've added them to my example). note the discrepancies between the code you posted (i just cleaned it up) and mine. – Owen Oct 16 '08 at 01:41

score 0 · Answer 5 · answered Oct 16 '08 at 03:05

        <?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.

Hi Andy. Thanks. In order to stop the php errors I needed to amend your code by removing the sigle quotes in ($row['req_type'] and add / slashes before the double quotes. That done, all I got in the drop down list was one '>'. Is there code I can add to each option rather than use an array? Nigel — , Oct 16 '08 at 11:26
I know I should separate the HTML from the PHP but as I haven't throughout the whole site could you recommend options that will work without the separation? Also, I know I should be using HTMLENTITIES but when I do I get the word HTMLENTITIES in the dispayed value? I know, I'm a bit of a dunce Nigel — , Oct 16 '08 at 11:30

score 0 · Answer 6 · edited Oct 17 '08 at 15:45

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

Brad · Answer 7 · 2008-10-16T02:02:34.567

-1

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>

edited Oct 16 '08 at 02:02

answered Oct 15 '08 at 19:07

Brad

12,054
44
118
187

Hi Brad. Thanks, but you don't list the options anywhere? The options are not held in the database, only the selected value. – Oct 16 '08 at 01:20
Thanks for the clarification. I wondered where the options were coming from. I have a table in one of my web sites that stores all the different items, that will be automatically loaded into a select drop down box, which is nice because I also refer to that table to do other functions. – Brad Oct 16 '08 at 02:04
Thanks again Brad. I had to take out the single quotes on req_type to stop the whitespace error and add \ slashes before the double quotes. But, that done I get the list of options dispayed outside the dropdown and inside the dropdown ';} ? – Oct 16 '08 at 12:08

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

7 Answers7