Jump to content

Listing Categories and Sub categoris with PHP and Mysql


thara
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

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>';
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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";
}
Link to comment
Share on other sites

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 by thara
Link to comment
Share on other sites

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
      •  
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by thara
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Muddy_Funster
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

@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;
        
    }
}
Link to comment
Share on other sites

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";
	}  
}
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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";
	}  
}
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

  • Solution

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 by Barand
Link to comment
Share on other sites

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

post-3105-0-44652200-1434830168_thumb.png

Edited by Barand
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.