Creating a Multi-Level Listbox 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.