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>';
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.
Also check out:
-
Aamir khan
-
sudan
-
snipe
-
widi
-
Rishikesh


