php mysql drop down list

Question

I have a PHP form that shows data from a MySQL table. Each row obviously has different data, what I want to do is have a drop down list that displays data relevant to the data entry in each row.

for example, lets say I have two tables. Fruit and Fruit_Colors, as below:

enter image description here

so if my PHP Form was displayed as below, the MySQL data named fruits would display the data in the Fruit column. The color would then be fetched from the Fruit_Colors table depending on the PHP form output value in field 'Fruit'. so the drop down list for each row will be different.

enter image description here

my PHP form table syntax is:

<table id="hor-minimalist-a">
    <tr>
        <th>ID</th>
        <th>Fruit</th>
        <th>Color</th>
        </tr>
<? while($row = $fruits->fetch(PDO::FETCH_ASSOC)) { ?>
    <tr>
        <td><? echo $row['id']; ?></td>
        <td><? echo $row['fruit']; ?></td>
        <td><SELECT NAME="fruitcolor" id="fruitcolor">
            <OPTION VALUE=0 >
             *// what goes here???*
            </option>
            </SELECT> 
        </td>
    </tr>
<? } ?>
</table>

Any advice how I can complete this would be appreciated. remember this table could be up to 50 rows so need a dynamic way of passing the 'fruit' value to the drop down list.

Syntax I know for the drop down list populations is:

    function fruitcolor_dropdown($db)  
  {    
    $result = $db->query("select color from Fruit_Color where Fruit=*'outputted value'*"); 
    return $result; 
  } 
  $colors= fruitcolor_dropdown($db); 
  while($row = $colors->fetch(PDO::FETCH_ASSOC)) {
  $color=$row["color"];
  $optionsfruitcolors.="<OPTION VALUE=\"$color\">".$color; 
  }

Advice appreciated as always. Thanks and Regards.

Good DB design would have the fruit_color table using fruitID instead of fruit_name as the linking field. You should also have a unique ID field on this table, otherwise it would be impossible to delete a record or correct a spelling (eg you have Yello bananas in the table, but how would you fix that without a reference ID?) — SDC
– SDC, Commented Jan 3, 2013 at 13:02
@AmazingDreams, thanks for your time. any code snippet example? — Smudger
– Smudger, Commented Jan 3, 2013 at 13:09
Thanks @SDC, example given is just a simplified example of what I want to achieve, not my actual database by any means. appreciate the input though. — Smudger
– Smudger, Commented Jan 3, 2013 at 13:10

Suresh Kamrushi · Accepted Answer · 2013-01-03 13:47:55Z

1

It will be bad idea to connect and fire query two time when you can do it in single query- you can see demo

$query = "select f.*,group_concat(color SEPARATOR '|') as fcolor from fruit F Left join fruit_color fc using (fruit) group by fc.fruit";

Above will be your query and you will loop it like below:

<? while($row = $fruits->fetch(PDO::FETCH_ASSOC)) { ?>
    <tr>
        <td><? echo $row['id']; ?></td>
        <td><? echo $row['fruit']; ?></td>
        <td><SELECT NAME="fruitcolor" id="fruitcolor">
            <OPTION VALUE=0 >
             <?php 
               $array = explode("|", $fcolor);
               $count = count($array);
               for($loop=0;$loop<$count;$loop) {
                 echo "<option>".$array[$loop]."</option>";
               }                                       
              ?>
            </option>
            </SELECT> 
        </td>
    </tr>
<? } ?>

edited Jan 3, 2013 at 13:47

answered Jan 3, 2013 at 13:10

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Smudger Over a year ago

Hi @Suresh, thanks for your time. any chance you can elaborate on what is happening here, way over my head. Thanks again.

Suresh Kamrushi Over a year ago

@Smudger: if you run query you will get data fruitname (Apple), color(red|green) in a signle row because of group_concat. And in your while loop you can create your dropdown by just exploding it by "|"(pipe). So no need to fire query two times.

Suresh Kamrushi Over a year ago

@Smudger: please have a look at the demo link.

Smudger Over a year ago

Thanks Suresh, Awesome logic, thank you. will give it a go now and can use this in a whole bunch of other instances. Thank you

Smudger Over a year ago

Hi @Suresh, trying to apply the PHP portion of the logic but this is causing my browser to 'hang'. the MySQL query result field 'fruit' has the array with the | separator. in the php I have the same syntax as you just with $fcolor being replaced by $row['fruit']; Any ideas why this would hang? Am I missing something, thanks again for your time.

phpisuber01 · Accepted Answer · 2013-01-03 13:06:19Z

1

Check this out. We create a function to generate the dropdown options. It accepts the DB and the fruit as parameters --> loops through and makes the DOM --> outputs it to the browser.

PHP Function

function getColors($db, $fruit)
{
    $result = $db->query(
            sprintf("select color from Fruit_Color where Fruit = '%s'",
                    $fruit
            )
    );

    $output = '';
    while($row = $result->fetch(PDO::FETCH_ASSOC))
    {
        $output .= sprintf(
                '<option value="%s">%s</option>', 
                $row['color'], 
                $row['color']
        );
    }

    return $output;
}

Template

<? while($row = $fruits->fetch(PDO::FETCH_ASSOC)) { ?>
    <tr>
        <td><? echo $row['id']; ?></td>
        <td><? echo $row['fruit']; ?></td>
        <td><SELECT NAME="fruitcolor" id="fruitcolor">
             <?php echo getColors($db, $row['fruit']); ?>
            </SELECT> 
        </td>
    </tr>
<? } ?>

answered Jan 3, 2013 at 13:06

phpisuber01

7,7453 gold badges25 silver badges26 bronze badges

2 Comments

Smudger Over a year ago

Hi @phpisuber01, thanks for the reply. works 100%. thank you. question, how efficient is this method as it is completing one MySQL query per row. 500 rows = 500 queries? is this best practice? Thanks again

phpisuber01 Over a year ago

@Smudger It's not the most efficient thing ever, no. Unless you get insane with it you shouldnt have an issue. The best practice would be a different method to store the colors and/or a SQL JOIN to access the colors and the fruit in a single query and handle it from there. I am not as good with SQL as I'd like to be, so I cannot provide much direction there. If you look at Suresh's answer below he has an example of a SQL query to access both in one shot.

Sujit Singh · Accepted Answer · 2013-01-03 13:19:37Z

0

use this in selectbox

function fruitcolor_dropdown($db)  
{    
   $result = $db->query("select color from Fruit_Color where Fruit=*'outputted value'*"); 
   return $result;
   while($row = $colors->fetch(PDO::FETCH_ASSOC)) {
      $color=$row["color"];
      $optionsfruitcolors.="<OPTION VALUE=\"$color\">".$color; 
   }  
} 
<select name="fruitcolor" id="fruitcolor">
   <?php  $colors= fruitcolor_dropdown($db); ?> 
</select>

edited Jan 3, 2013 at 13:19

Sujit Singh

9211 gold badge10 silver badges20 bronze badges

answered Jan 3, 2013 at 13:02

nickle

5,2861 gold badge16 silver badges11 bronze badges

1 Comment

Smudger Over a year ago

Thanks, How do I pass the fruit to the MySQL statement to replace 'outputted value' in question?

Collectives™ on Stack Overflow

php mysql drop down list

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related