Listing Categories and Sub categoris with PHP and Mysql

[thara]

I have used hierarchical data modal to store animals in database. Animal have only main and subcategory, and this is the result when I retrieving the Full Tree of Animals.
 

    SELECT t1.name AS lev1,
                 t2.name as lev2,
                 t3.name as lev3
    FROM categories AS t1
        LEFT JOIN categories AS t2
            ON t2.parent = t1.category_id
        LEFT JOIN categories AS t3
            ON t3.parent = t2.category_id
    WHERE t1.name = 'Pets';

    +------+------+-------------------+
    | lev1 | lev2 | lev3              |
    +------+------+-------------------+
    | Pets | Dogs | Bulldog           |
    | Pets | Dogs | Bullmastiff       |
    | Pets | Dogs | Chow Chow         |
    | Pets | Dogs | Cocker Spaniel    |
    | Pets | Dogs | German Shepherd   |
    | Pets | Dogs | Gordon Setter     |
    | Pets | Cats | American Bobtail  |
    | Pets | Cats | Balinese          |
    | Pets | Cats | Birman            |
    | Pets | Cats | British Shorthair |
    | Pets | Cats | Burmese           |
    +------+------+-------------------+
    11 rows in set (0.04 sec)

My question is, I'm having trouble displaying this information as an unordered list. My expecting result would be something like this:

    <ul class="list-unstyled categorychecklist ">
        <li><input type="checkbox" value=""> Dogs
            <ul class="children">
                <li><input type="checkbox" value=""> Bulldog</li>
                <li><input type="checkbox" value=""> Bullmastiff</li>
                <li><input type="checkbox" value=""> Chow Chow</li>
                <li><input type="checkbox" value=""> Cocker Spaniel</li>
                <li><input type="checkbox" value=""> German Shepherd</li>
                <li><input type="checkbox" value=""> Gordon Setter</li>
            </ul>        
        </li>    
        <li><input type="checkbox" value=""> Cats
            <ul class="children">
                <li><input type="checkbox" value=""> American Bobtail</li>
                <li><input type="checkbox" value=""> Balinese</li>
                <li><input type="checkbox" value=""> Birman</li>
                <li><input type="checkbox" value=""> British Shorthair</li>
                <li><input type="checkbox" value=""> Burmese</li>
            </ul>        
        </li>
        <li><input type="checkbox" value=""> Birds</li>
    </ul>   

 Hope somebody may help me out.

Thanks in advance.

[Ch0cu3r]

Build a multidimensional array from the query result, using lev1 and lev2 values as the keys

$categories = array();
while($row = mysqli->fetch_assoc())
{
    $categories[ $row['lev1'] ][ $row['lev2'] ][] = $row['lev3'];
}

Now you can use a series of foreach loops to create the unordered list, 

echo '<ul class="list-unstyled categorychecklist ">';
foreach($categories['pets'] as $lev2_category => $lev2_categories)
{
    echo '
    <li><input type="checkbox" value="" />' . $lev2_category . '
        <ul class="children">';

    foreach($lev2_categories as $lev3_value)
    {
        echo '<li><input type="checkbox" value="" />'.$lev3_value.'</li>';
    }

    echo '
        </ul>
    </li>';
}
echo '</ul>';

[Muddy_Funster]

here's a tried and tested answer that's almost the same as what @Ch0cu3r suggested, only real difference is that my code doesn't hard set the output to use "Pets" and that the output is stored in a variable before being rendered to the page.

$results = array(
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'Bulldog'),
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'BullMastiff'),
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'Chow Chow'),
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'Cocker Spaniel'),
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'German Shepherd'),
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'Gordon Setter'),
array('Lev1'=>'Pets', 'Lev2'=>'Dogs', 'Lev3'=>'American Bobtail'),
array('Lev1'=>'Pets', 'Lev2'=>'Cats', 'Lev3'=>'Balinese'),
array('Lev1'=>'Pets', 'Lev2'=>'Cats', 'Lev3'=>'Birman'),
array('Lev1'=>'Pets', 'Lev2'=>'Cats', 'Lev3'=>'British Shorthair'),
array('Lev1'=>'Pets', 'Lev2'=>'Cats', 'Lev3'=>'Burmese')
);
$orderTemp = array();
foreach ($results as $row){
        $orderTemp[$row['Lev1']][$row['Lev2']][] = $row['Lev3']; //creates a multidimensional array that reflects the hierarchy
}
$list = ""; //init string variable
foreach($orderTemp as $first=>$second){ // start iterating through the top level array (pets)
    $list .= "<ul class=\"list-unstyled categorychecklist \">"; //create the initial ul
    foreach ($second as $secKey=>$third){ //start iterating through the second level array (cats and dogs)
        $list .="<li><input type=\"checkbox\" value=\"\"> {$secKey}"; //create initial checkbox level
        $list .="<ul class=\"children\">"; // open sub list
        foreach($third as $triKey=>$value){ // iterate through the third level (names of breeds)
            $list .= "<li><input type=\"checkbox\" value=\"\"> {$value}</li>"; // create checkboxes for breeds
        }
    $list .="</li></ul>"; //close top level checkbox items
    }
    $list.="</li></ul>"; //close initial ul
}
echo $list; // render the output to page

[Barand]

Or you can use recursion

$res = $db->query($sql);
while (list($lev1,$lev2,$lev3) = $res->fetch_row()) {
   $cats[$lev1][$lev2][] = $lev3;
}

displayList($cats);

function displayList($cats) {
        echo "<ul>\n";
        foreach ($cats as $k=>$v) {
            if (is_array($v)) {
                echo "<li>$k</li>\n";
                displayList($v);
            }
            else echo "<li>$v</li>\n";
        }
        echo "</ul>\n";
}

[Muddy_Funster]

 

Or you can use recursion

 

Isn't that excessive overhead for a known, fixed depth, MultiDim Array?

[thara]

Thank you for all 3 replies and all 3 answers working me partly.

 

From first two answers, I can get the same output. Problem is, if main category doesn't have its sub category, then echoing an empty checkbox to that main category.

 

So I tried to avoid from it something like this, But it doesn't work for me.

 

check this screenshot : http://img42.com/IHEz6+

                        echo '<ul class="list-unstyled categorychecklist ">';
                        foreach($categories['Pets'] as $lev2_category => $lev2_categories) {
                            echo '<li><input type="checkbox" value="" /> ' . $lev2_category;
                            
                            if(!empty($lev2_categories)) {    
                                echo '<ul class="children">';
                                
                                foreach($lev2_categories as $lev3_value) {
                                    echo '<li><input type="checkbox" value="" /> '.$lev3_value.'</li>';
                                }
                                
                                echo '</ul></li>';
                            } else {                    
                                
                                echo '<li><input type="checkbox" value="" /> ' . $lev2_category.'</li>';
                            }                                    
                        }
                        echo '</ul>';

Edited June 19, 2015 by thara

[thara]

When using @Barand answer I can get an output something similar to this. But I don't need to have Main category in this case its "Pets".

• Pets
  • Dogs
    • Bulldog
    • Bullmastiff
    • Chow Chow
    • Cocker Spaniel
    • German Shepherd
    • Gordon Setter
  • Cats
    • American Bobtail
    • Balinese
    • Birman
    • British Shorthair
    • Burmese
  • Birds
    •  
  • Fish
    •  
  • Reptile
    •  

[Ch0cu3r]

 

Problem is, if main category doesn't have its sub category, then echoing an empty checkbox to that main category.

Whats lev3 set to when there is no sub category? Your query should be returning NULL and so checking to see if $lev2_categories is not empty should work.

[Barand]

Isn't that excessive overhead for a known, fixed depth, MultiDim Array?

Probably.

 

On reflection I would prefer to recurse with this simpler query and method though, and it should work for any number of levels and doesn't display the top level.

$sql = "SELECT category_id
		, name
		, IFNULL(parent, 0)
        FROM category";
$res = $db->query($sql);
while (list($id,$name,$parent) = $res->fetch_row()) {
   $cats[$parent][$id] = $name;
}

displayList($cats, 0);

function displayList(&$cats, $parent) {
	if ($parent==0) {
		foreach ($cats[$parent] as $id=>$nm) {
			displayList($cats, $id);
		}
	}
	else {
		echo "<ul>\n";
		foreach ($cats[$parent] as $id=>$nm) {
			echo "<li>$nm</li>\n";
			if (isset($cats[$id])) {
				displayList($cats, $id);
			}
		}
		echo "</ul>\n";
	}  
}

With your data, gives

<ul>
<li>Dogs</li>
<ul>
<li>Bulldog</li>
<li>Bullmastiff</li>
<li>Chow Chow</li>
<li>Cocker Spaniel</li>
<li>German Shepherd</li>
<li>Gordon Setter</li>
</ul>
<li>Cats</li>
<ul>
<li>American Bobtail</li>
<li>Balinese</li>
<li>Birman</li>
<li>British Shorthair</li>
<li>Burmese</li>
</ul>
<li>Birds</li>
<li>Fish</li>
<li>Reptiles</li>
</ul>

Edited June 19, 2015 by Barand

[thara]

this is how my category data look like..

mysql> SELECT t1.name AS lev1,
    ->                   t2.name as lev2,
    ->                   t3.name as lev3
    -> FROM categories AS t1
    ->  LEFT JOIN categories AS t2
    ->          ON t2.parent = t1.category_id
    ->  LEFT JOIN categories AS t3
    ->          ON t3.parent = t2.category_id
    -> WHERE t1.name = 'Pets';
+------+---------+-------------------+
| lev1 | lev2    | lev3              |
+------+---------+-------------------+
| Pets | Dogs    | Bulldog           |
| Pets | Dogs    | Bullmastiff       |
| Pets | Dogs    | Chow Chow         |
| Pets | Dogs    | Cocker Spaniel    |
| Pets | Dogs    | German Shepherd   |
| Pets | Dogs    | Gordon Setter     |
| Pets | Cats    | American Bobtail  |
| Pets | Cats    | Balinese          |
| Pets | Cats    | Birman            |
| Pets | Cats    | British Shorthair |
| Pets | Cats    | Burmese           |
| Pets | Birds   | NULL              |
| Pets | Fish    | NULL              |
| Pets | Reptile | NULL              |
+------+---------+-------------------+
14 rows in set (0.06 sec)

Edited June 19, 2015 by thara

[thara]

Whats lev3 set to when there is no sub category? Your query should be returning NULL and so checking to see if $lev2_categories is not empty should work.

 

 

This is my updated code and output is still same. So what would be the problem?

echo '<ul class="list-unstyled categorychecklist ">';
foreach($categories['Pets'] as $lev2_category => $lev2_categories) {
    echo '<li><input type="checkbox" value="" /> ' . $lev2_category;
    
    if(!empty($lev2_categories) && $lev2_categories != NULL) {    
        echo '<ul class="children">';
        
        foreach($lev2_categories as $lev3_value) {
            echo '<li><input type="checkbox" value="" /> '.$lev3_value.'</li>';
        }
        
        echo '</ul>';
    } else {                    
        echo '</li>';
        //echo '<li><input type="checkbox" value="" /> ' . $lev2_category.'</li>';
    }                                    
}
echo '</ul>';

Thanks in advance.

[Muddy_Funster]

Here's an update to my code that has some conditional checks in place to stop the empty items:

$orderTemp = array();
foreach ($results as $row){
    if(count($row) == 3 ){
        $orderTemp[$row['Lev1']][$row['Lev2']][] = $row['Lev3'];
    }
    if(count($row) == 2 ){
        $orderTemp[$row['Lev1']][$row['Lev2']] = null;
    }
    
}
$list = "";
foreach($orderTemp as $first=>$second){
    $list .= "<ul class=\"list-unstyled categorychecklist \">";
        foreach ($second as $secKey=>$third){
        $list .="<li><input type=\"checkbox\" value=\"\"> {$secKey}";
        $list .="<ul class=\"children\">";
        if(is_array($third)){
            foreach($third as $triKey=>$value){
            $list .= "<li><input type=\"checkbox\" value=\"\"> {$value}</li>";
        }
        }
    $list .="</li></ul>";
    }
    $list.="</li></ul>";
}

echo $list;

 

Edit : added check to stop warning during third foreach

Edited June 19, 2015 by Muddy_Funster

[thara]

 

On reflection I would prefer to recurse with this simpler query and method though, and it should work for any number of levels and doesn't display the top level.

 

Thank you for your answer. Its working for me. But problem is when I trying to add CSS classes to these <ul> list. Can you tell me how can we add these classes?

 

Thank you.

[thara]

@Muddy_Funster, Can you tell me how I modify this section of your code to match with my while loop.

$orderTemp = array();
foreach ($results as $row){
    if(count($row) == 3 ){
        $orderTemp[$row['Lev1']][$row['Lev2']][] = $row['Lev3'];
    }
    if(count($row) == 2 ){
        $orderTemp[$row['Lev1']][$row['Lev2']] = null;
    }
    
}

This is how my while loop looks like. I am using mysqli prepared Statements for my select query. 

$prep_stmt =   "SELECT t1.name AS lev1,
                                             t2.name as lev2,
                                             t3.name as lev3
                                FROM categories AS t1
                                    LEFT JOIN categories AS t2
                                        ON t2.parent = t1.category_id
                                    LEFT JOIN categories AS t3
                                        ON t3.parent = t2.category_id
                                WHERE t1.name = 'Pets'";
                                                            
$stmt = $mysqli->prepare($prep_stmt);

if ($stmt) {
    // Execute the prepared query.
    $stmt->execute();    
    $stmt->store_result();
        
    // get variables from result.
    $stmt->bind_result($lev1, $lev2, $lev3);

    $categories = array();

    // Fetch all the records:
    while ($stmt->fetch()) {
        
        $categories[$lev1][$lev2][] = $lev3;
        
    }
}

[Ch0cu3r]

Use

while ($stmt->fetch())
{
    if(empty($lev3))
    {
        $categories[$lev1][$lev2] = null;
    }
    else
    {
        $categories[$lev1][$lev2][] = $lev3;
    }
}

[Barand]

Thank you for your answer. Its working for me. But problem is when I trying to add CSS classes to these <ul> list. Can you tell me how can we add these classes?

 

Thank you.

 

Pass a level value down the function calls and use that

function displayList(&$cats, $parent, $level=0) {
	switch ($level) {
		case 0: $class = "level0"; break;
		case 1: $class = "level1"; break;
		case 2: $class = "level2"; break;
	}
	if ($parent==0) {
		foreach ($cats[$parent] as $id=>$nm) {
			displayList($cats, $id);
		}
	}
	else {
		echo "<ul>\n";
		foreach ($cats[$parent] as $id=>$nm) {
			echo "<li>$nm</li>\n";
			if (isset($cats[$id])) {
				displayList($cats, $id, $level+1);  //increment level
			}
		}
		echo "</ul>\n";
	}  
}

[thara]

Pass a level value down the function calls and use that

 

 

@Barand, I tried with your updated answer. This is rendered HTML from it.

<ul>
    <li>Dogs</li>
    <ul>
        <li>Bulldog</li>
        <li>Bullmastiff</li>
        <li>Chow Chow</li>
        <li>Cocker Spaniel</li>
        <li>German Shepherd</li>
        <li>Gordon Setter</li>
    </ul>
    <li>Cats</li>
        <ul>
        <li>American Bobtail</li>
        <li>Balinese</li>
        <li>Birman</li>
        <li>British Shorthair</li>
        <li>Burmese</li>
        </ul>
    <li>Birds</li>
</ul>

But I want to have my HTML something like this.

<ul class="list-unstyled categorychecklist ">
    <li><input type="checkbox" value=""> Dogs
        <ul class="children">
            <li><input type="checkbox" value=""> Bulldog</li>
            <li><input type="checkbox" value=""> Bullmastiff</li>
            <li><input type="checkbox" value=""> Chow Chow</li>
            <li><input type="checkbox" value=""> Cocker Spaniel</li>
            <li><input type="checkbox" value=""> German Shepherd</li>
            <li><input type="checkbox" value=""> Gordon Setter</li>
        </ul>
    </li>
    <li><input type="checkbox" value=""> Cats
        <ul class="children">
            <li><input type="checkbox" value=""> American Bobtail</li>
            <li><input type="checkbox" value=""> Balinese</li>
            <li><input type="checkbox" value=""> Birman</li>
            <li><input type="checkbox" value=""> British Shorthair</li>
            <li><input type="checkbox" value=""> Burmese</li>
        </ul>
    </li>
    <li><input type="checkbox" value=""> Birds
        <ul class="children"></li></ul>
    </li>
</ul>

With your function, I confuse how to separate main and nested <ul> with their classes.

 

Any idea would be greatly appreciating.

 

Thanks in advance.

[Barand]

The value of "$level" will tell you whether is the main or nested.

[thara]

@Barand, Still I couldn't get what you have said?

[Barand]

Like this

function displayList(&$cats, $parent, $level=0) {
	switch ($level) {
		case 0: $class = "list-unstyled categorychecklist"; break;
		case 1: $class = "children"; break;
		case 2: $class = "children2"; break;
	}
	if ($parent==0) {
		foreach ($cats[$parent] as $id=>$nm) {
			displayList($cats, $id);
		}
	}
	else {
		echo "<ul class='$class'>\n";
		foreach ($cats[$parent] as $id=>$nm) {
			echo "<li><input type='checkbox' value=''> $nm</li>\n";
			if (isset($cats[$id])) {
				displayList($cats, $id, $level+1);  //increment level
			}
		}
		echo "</ul>\n";
	}  
}

[thara]

@Barand, Yes its working nicely. Thank you very much for the great solution like this. I am trying and trying to get the logic of this function and how its work, but still I couldn't.... If you have a time, kindly explain this function and how its working. It will be greatly helped me.

 

Thank you very much again.

[Barand]

First of all, from the query create an array where the keys are the parent and each value is an array of that parent's children

Array
(
    [0] => Array
        (
            [1] => Pets
        )

    [1] => Array
        (
            [2] => Dogs
            [3] => Cats
            [4] => Birds
            [5] => Fish
            [6] => Reptiles
        )

    [2] => Array
        (
            [11] => Bulldog
            [12] => Bullmastiff
            [13] => Chow Chow
            [14] => Cocker Spaniel
            [15] => German Shepherd
            [16] => Gordon Setter
        )

    [3] => Array
        (
            [17] => American Bobtail
            [18] => Balinese
            [19] => Birman
            [20] => British Shorthair
            [21] => Burmese
        )

)

The function is recursive (ie it calls itself). The function is passed the above array and a parent id.

 

So if "1" is passed as the parent it starts a new <ul> group then processes the children of parent 1.

The first child is

[2] => Dogs

It outputs this as a list item and checks if id 2 has any children  ( isset($cats[2]) ). If it has children it calls the same function passing "2" as the parent and the process is repeated.

So the result is to output each category followed by a list of its children. If those children have children, they are listed after the parent.

 

An advantage of this approach is that if you now add "Amphibians" as a subcat of "Reptile" and that subcat has further subcats of "Frog", "Toad", "Alligator" then it will still work. With LEFT JOIN approach you would have to change the query and processing if another level were introduced.

Edited June 20, 2015 by Barand

[Barand]

Just to illustrate:

+-------------+-------------------+--------+
| category_id | name              | parent |
+-------------+-------------------+--------+
|           1 | Pets              |      0 |
|           2 | Dogs              |      1 |
|           3 | Cats              |      1 |
|           4 | Birds             |      1 |
|           5 | Fish              |      1 |
|           6 | Reptiles          |      1 |

added these:

|          22 | Amphibians        |      6 |
|          23 | Frogs             |     22 |
|          24 | Toads             |     22 |
|          25 | Alligator         |     22 |
|          26 | Salt-water        |     25 |
|          27 | Fresh-water       |     25 |
+-------------+-------------------+--------+

Edited June 20, 2015 by Barand