Ad

Nested Dropdown

- 1 answer

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.

Did anyone get an elegant solution for doing this?

Ad

Answer

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>";
Ad
source: stackoverflow.com
Ad