Snipe.Net Geeky, sweary things.

Creating a Multi-Level Listbox in PHP/mySQL

C

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.

[sourcecode language=’php’]// $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;
}[/sourcecode]

You would call the function using something like this:

[sourcecode language=’php’] echo ‘

About Me

I’m a tech geek/dev/infosec-nerd/scuba diver/blacksmith/sword-fighter/crime fighter/ENTP/warcrafter/activist. I run Grokability, Inc, and run several open source projects, including Snipe-IT Asset Management. Tweet at me @snipeyhead or read more