Creating a Multi-Level Listbox in PHP/mySQL
Posted on June 19, 2006 by snipe in PHP/mySQL
This lets you create a nested multi-level category menu through PHP and mySQL. Using a recursive function, we can display an unlimited number of nested categories, for a drop down box that might look like this:
Fruit
– Apples
—->Red Delicious
—->Granny Smith
Database:
This code is assuming that you have a database table containing your menu options that looks something like this:
Table categories: +--------+----------------------+-----------+ | id | name | parent_id | +--------+----------------------+-----------+ | 0 | Main Category 1 | 0 | | 1 | Main category 2 | 0 | | 2 | Subcategory 1 | 1 | | 3 | Subcategory 2 | 1 | | 4 | Main category 3 | 0 | +--------+----------------------+-----------+
It is also assuming that the name of your listbox is “cat_id”. This is easily changed, mind you – you just have to change the select code down at the bottom and the “$categories = $_POST['cat_id'];” line to reflect whatever you’re naming it.
// $current_cat_id: the current category id number
// $count: just a counter, call it as 0 in your function call and forget about it
/* GET THE DROP DOWN LIST OF CATEGORIES */
function get_cat_selectlist($current_cat_id, $count) {
static $option_results;
// if there is no current category id set, start off at the top level (zero)
if (!isset($current_cat_id)) {
$current_cat_id =0;
}
// increment the counter by 1
$count = $count+1;
// query the database for the sub-categories of whatever the parent category is
$sql = 'SELECT id, name from categories where parent_id = '.$current_cat_id;
$sql .= 'order by name asc ';
$get_options = mysql_query($sql);
$num_options = mysql_num_rows($get_options);
// our category is apparently valid, so go ahead €¦
if ($num_options > 0) {
while (list($cat_id, $cat_name) = mysql_fetch_row($get_options)) {
// if its not a top-level category, indent it to
//show that its a child category
if ($current_cat_id!=0) {
$indent_flag = '--';
for ($x=2; $x< =$count; $x++) {
$indent_flag .= ' >';
}
}
$cat_name = $indent_flag.$cat_name;
$option_results[$cat_id] = $cat_name;
// now call the function again, to recurse through the child categories
get_cat_selectlist($cat_id, $count );
}
}
return $option_results;
}
You would call the function using something like this:
echo '<select name="cat_id">';
echo '<option value="">-- Select -- </option>';
$get_options = get_cat_selectlist(0, 0);
if (count($get_options) > 0){
$categories = $_POST['cat_id'];
foreach ($get_options as $key => $value) {
$options .="<option value=\"$key\"";
// show the selected items as selected in the listbox
if ($_POST['cat_id'] == "$key") {
$options .=" selected=\"selected\"";
}
$options .=">$value</option>\n";
}
}
echo $options;
echo '</select>';
Also check out:
If you think this article kicked ass, subscribe to the RSS feed or follow me on Twitter! Share with your friends, or leave a comment below (or better still, do both!) My entire concept of self-worth is in your hands, so that makes you kind of a big deal. Srsly.





Hi,
this is very nice tutorial. it really helped me.
thanks
if ($current_cat_id!=0) {
30.
$indent_flag = “ â€;
31.
for ($x=2; $x â€;
33.
}
what its???
this is very nice code but there r some problem
$indent_flag = “ â€;
for ($x=2; $x<=$count; $x++) {
$indent_flag .= “–> â€;
what is this “ â€;
please fix this problem as soon as posible.
I am waiting to solve this problem
thanks.
It has been fixed. You might try asking instead of demanding next time. It was a character encoding issue, that's all.
HI,
This is very simple and easy example. I would like to suggest that every one should use it. It realy help me.
Thanks
Thank you. Saved me ton of time.
Very useful code snippet. Thanks!
Hello,
This code is really helpful.
Only one small mistake.
In code there is one for loop as 'for ($x=2; $x< =$count; $x++)'
It should be 'for ($x=2; $x <= $count; $x++)'.
That's likely aq formatting quirk in WordPress.