user3779493 user3779493 - 6 months ago 185
SQL Question

How to display categories, subcategories, sub-subcategories in select list - php/mysql?

I want to display categories, subcategories and sub-subcategories in a select list (drop-down list) like the way the WordPress shows in its admin panel. First look at my database table (tb_categories) -

Database Table

I want the following output in HTML form -

Output

The two items "None" and "Uncategorized" are hardcoded in the code. I am wondering how to display categories and their subcategories in hierarchical order using select list options.

I am trying with the following sql query in which I am using self join. Here it is -

SELECT
`cat`.`category_name` AS 'category name',
`cat2`.`category_name` AS 'parent category'
FROM
`tb_categories` AS `cat`
LEFT JOIN `tb_categories` AS `cat2` ON `cat`.`category_parent` = `cat2`.`category_id`
ORDER BY
'parent category'


And the output it is giving is -

Array
(
[0] => Array
(
[0] => My Parent Category
[category name] => My Parent Category
[1] =>
[parent category] =>
)

[1] => Array
(
[0] => Parent Category 2
[category name] => Parent Category 2
[1] =>
[parent category] =>
)

[2] => Array
(
[0] => Parent Category 3
[category name] => Parent Category 3
[1] =>
[parent category] =>
)

[3] => Array
(
[0] => My Child Category
[category name] => My Child Category
[1] => My Parent Category
[parent category] => My Parent Category
)

[4] => Array
(
[0] => Sports
[category name] => Sports
[1] =>
[parent category] =>
)

[5] => Array
(
[0] => Cricket is best
[category name] => Cricket is best
[1] => Sports
[parent category] => Sports
)

[6] => Array
(
[0] => AJAX
[category name] => AJAX
[1] =>
[parent category] =>
)

[7] => Array
(
[0] => hockey is best
[category name] => hockey is best
[1] => Sports
[parent category] => Sports
)

)


I don't know and even not sure how can I display above data in that select list. How we do that? How can we do it using joins? If we use joins then do we need some array to store and sort the results? And also how do we do it using several queries in a loop? Which method will be best?

Answer

Assuming your given array is in $array you can use this. But as I told you already you should select the ids to handle categories with the same name and to use them as option values in your selectbox:

  $options = get_options($array);
  echo "<select>";
  foreach($options as $val) {
    echo "<option>".$val."</option>";
  }
  echo "</select>";

  function get_options($array, $parent="", $indent="") {
    $return = array();
    foreach($array as $key => $val) {
      if($val["parent category"] == $parent) {
        $return[] = $indent.$val["category name"];
        $return = array_merge($return, get_options($array, $val["category name"], $indent."&nbsp;&nbsp;&nbsp;"));
      }
    }
    return $return;
  }

Assuming that you now have the ids in your array as "category_id" and "parent_category_id" you can use this. The "x" prior to the key in $return is just to avoid that php changes your keys, because they are numeric.

  $options = get_options($array);
  echo "<select>";
  foreach($options as $key => $val) {
    echo "<option value='".substr($key,1)."'>".$val."</option>";
  }
  echo "</select>";

  function get_options($array, $parent=0, $indent="") {
    $return = array();
    foreach($array as $key => $val) {
      if($val["parent_category_id"] == $parent) {
        $return["x".$val["category_id"]] = $indent.$val["category name"];
        $return = array_merge($return, get_options($array, $val["category_id"], $indent."&nbsp;&nbsp;&nbsp;"));
      }
    }
    return $return;
  }