Nested dropdown

Question

Hi all,

I'm building a form with php/mysql. I've got a table with a list of locations and sublocations. Each sublocation has a parent location. A column "parentid" references another locationid in the same table. I now want to load these values into a dropdown in the following manner:

--Location 1
----Sublocation 1
----Sublocation 2
----Sublocation 3
--Location 2
----Sublocation 4
----Sublocation 5

etc. etc.

Anyone got an elegant solution for doing this?

Answer 1

Are you looking for something like the OPTGROUP tag?

Answer 2

yes optgroup is useful - it's more how I transform my mysql dataset into the optgroup markup

Answer 3

optgroup is definitely the way to go. It's actually what it's for,

For example usage, view source of http://www.grandhall.eu/tips/submit/ - the selector under "Grandhall Grill Used".

Answer 4

You can use and space/dash indentation in the actual HTML. You'll need a recusrive loop to build it though. Something like:

<?php

$data = array(
    'Location 1'    =>    array(
        'Sublocation1',
        'Sublocation2',
        'Sublocation3'    =>    array(
            'SubSublocation1',
        ),
    'Location2'
);

$output = '<select name="location">' . PHP_EOL;

function build_items($input, $output)
{
    if(is_array($input))
    {
        $output .= '<optgroup>' . $key . '</optgroup>' . PHP_EOL;
        foreach($input as $key => $value)
        {
            $output = build_items($value, $output);
        }
    }
    else
    {
        $output .= '<option>' . $value . '</option>' . PHP_EOL;
    }

    return $output;
}

$output = build_items($data, $output);

$output .= '</select>' . PHP_EOL;

?>

Or something similar ;)

Answer 5

NOTE: This is only psuedo-code.. I didn't try running it, though you should be able to adjust the concepts to what you need.

$parentsql = "SELECT parentid, parentname FROM table";

 $result = mysql_query($parentsql);
 print "<select>";
 while($row = mysql_fetch_assoc($result)){
    $childsql = "SELECT childID, childName from table where parentid=".$row["parentID"];
    $result2 = mysql_query($childsql);
    print "<optgroup label=\".$row["parentname"]."\">";
    while($row2 = mysql_fetch_assoc($result)){
     print "<option value=\"".$row["childID"]."\">".$row["childName"]."</option>\n";
    }
    print "</optgroup>";
}
 print "</select>";

With BaileyP's valid criticism in mind, here's how to do it WITHOUT the overhead of calling multiple queries in every loop:

$sql = "SELECT childId, childName, parentId, parentName FROM child LEFT JOIN parent ON child.parentId = parent.parentId ORDER BY parentID, childName";  
$result = mysql_query($sql);
$currentParent = "";

print "<select>";
while($row = mysql_fetch_assoc($result)){
 if($currentParent != $row["parentID"]){
  if($currentParent != ""){
   print "</optgroup>";
  }
  print "<optgroup label=\".$row["parentName"]."\">";
  $currentParent = $row["parentName"];
 }

    print "<option value=\"".$row["childID"]."\">".$row["childName"]."</option>\n";
}
print "</optgroup>"
print "</select>";

Answer 6

cheers Wayne - that's exactly what I was trying to do but u've summed it up so elegantly

Answer 7

Ideally, you'd select all this data in the proper order right out of the database, then just loop over that for output. Here's my take on what you're asking for

<?php
/*
Assuming data that looks like this

locations
+----+-----------+-------+
| id | parent_id | descr |
+----+-----------+-------+
|  1 |      null | Foo   |
|  2 |      null | Bar   |
|  3 |         1 | Doe   |
|  4 |         2 | Rae   |
|  5 |         1 | Mi    |
|  6 |         2 | Fa    |
+----+-----------+-------+
*/

$result = mysql_query( "SELECT id, parent_id, descr FROM locations order by coalesce(id, parent_id), descr" );

echo "<select>";
while ( $row = mysql_fetch_object( $result ) )
{
    $optionName = htmlspecialchars( ( is_null( $row->parent_id ) ) ? "--{$row->descr}" : "----{$row->desc}r", ENT_COMPAT, 'UTF-8' );
    echo "<option value=\"{$row->id}\">$optionName</option>";
}
echo "</select>";

If you don't like the use of the coalesce() function, you can add a "display_order" column to this table that you can manually set, and then use for the ORDER BY.