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

Question

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) -

I want the following output in HTML form -

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 1

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;
  }

Answer 2

This is the simplest category table structure

+-------------------------+
| categories              |
+-------------------------+
| category_id   (int)     | PK
| parent_id     (int)     | Index
| category_name (varchar) |
| ...                     |
+-------------------------+

The function below will recursively fetch all categories and build a hierarchy. As you may see, you can choose a value to be preselected or you can fetch only a part of the hierarchy changing parent value.

function build_category_tree(&$output, $preselected, $parent=0, $indent=""){
  $r = mysql_query("
    SELECT category_id, category_name FROM categories WHERE parent_id = " . $parent . "
  ");

  while($c = mysql_fetch_array($r, MYSQL_ASSOC)){
    $selected = ($c["category_id"] == $preselected) ? "selected=\"selected\"" : "";
    $output .= "<option value=\"" . $c["category_id"] . "\" " . $selected . ">" . $indent . $c["category_name"] . "</option>";
    if($c["category_id"] != $parent){
      build_category_tree($output, $preselected, $c["category_id"], $indent . "&nbsp;&nbsp;");
    }
  }
}

Usage:

<?php 
build_category_tree($categories, 0); 
// if you want to preselect a value and start from some subcategory
build_category_tree($categories, 5, 2); 
?>

<!-- HTML -->
<select><?php echo $categories ?></select>
<!-- if you want to add some extra options -->
<select>
  <option value="-1">Choose a category</option> 
  <?php echo $categories ?>
</select>

Side note: Although this function looks elegant, do not use it if you have thousands of categories. Also, use MySQLi or PDO_MySQL extension in your final version.

Answer 3

Database View For Categories

$con = mysqli_connect("localhost","root","","categories"); 
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = mysqli_query($con,"SELECT `cat`.`categoryName` AS 'category name', `cat2`.`categoryName` AS 'parent category' FROM `product_category` AS `cat` LEFT JOIN `product_category` AS `cat2` ON `cat`.`parentId` = `cat2`.`categoryId` where `cat2`.`categoryName` !='NULL' order by `cat2`.`categoryName`");

  while($res=mysqli_fetch_array($sql,MYSQLI_ASSOC)){
  $cat[] = $res['category name'];
  $parent[]= $res['parent category'];
}
$parents = "";
for($i=0;$i<count($cat);$i++){
 if($parents!=$parent[$i]){
if($i!=0){ echo "</ul>"; }
 echo $parent[$i]."<ul><li>".$cat[$i]."</li>" ;
}else{

 echo "<li>".$cat[$i]."</li>" ;
}
$parents = $parent[$i];
}
echo "</ul>";

Output

Form More tutorials regarding php, ajax, jquery, mysql, interview questions. kindly see my blogs hackandphp.com