help, php drop down menu using hierarchical database

[kslakhani]

I want to create a drop down menu that display main categories then all child categories with all of their sub child list like the one shown in below pic

I am using a table named categories which uses hierarchical system like parent id, right id, left id and level looks like in below pic

here is my categoriesTest.php

<?php
include 'common.php';
$query = "SELECT cat_id, parent_id, cat_name FROM " .$DBPrefix. "categories ORDER BY cat_name";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
$items = mysql_fetch_assoc($res);
$html = '';
$parent = 0;
$parent_stack = array();

// $items contains the results of the SQL query
$children = array();
foreach ( $items as $item )
    $children[$items['parent_id']][] = $item;

while  ( $option = each( $children[$parent] ) ) 
{
    if ( !empty( $option ) )
    {
        // 1) The item contains children:
        // store current parent in the stack, and update current parent
        if ( !empty( $children[$option['value']['id']] ) )
        {
            $html .= '<li>' . $option['value']['title'] . '</li>';
            $html .= '<ul>'; 
            array_push( $parent_stack, $parent );
            $parent = $option['value']['id'];
        }
        // 2) The item does not contain children
        else
            $html .= '<li>' . $option['value']['title'] . '</li>';
    }
    // 3) Current parent has no more children:
    // jump back to the previous menu level
    else
    {
        $html .= '</ul>';
        $parent = array_pop( $parent_stack );
    }
}

// At this point, the HTML is already built
echo $html;

?>

the above code not working and am not sure it is the correct way.

can anyone help me out??

Thanks

[Jacques1]

So how is this “not working”? What's the problem?

 

Taking a wild guess:

• You only fetch the first row. To get all rows, you need to call mysql_fetch_assoc() repeatedly.
• The loop logic is flawed: When a category has no more children, the loop is immediately stopped. The check within the loop which should close the list and update the parent isn't reached.

I strongly recommend that you use recursion instead of the current iterative approach. It will make the code much easier to understand.

[kslakhani]

So how is this “not working”? What's the problem?

 

Taking a wild guess:

• You only fetch the first row. To get all rows, you need to call mysql_fetch_assoc() repeatedly.
• The loop logic is flawed: When a category has no more children, the loop is immediately stopped. The check within the loop which should close the list and update the parent isn't reached.

I strongly recommend that you use recursion instead of the current iterative approach. It will make the code much easier to understand.

Hi Jacques,

the above code gives me nothing

i am completely blank on recursive stuff

Edited September 27, 2015 by kslakhani

[Jacques1]

Start by fixing the database part: Make sure the query is correct, and then call mysql_fetch_assoc() repeatedly until you've fetched all rows into the $children array. You can check the array with var_dump().

 

When that's done, you can go on with the recursive function. It will look like this (as pseudo-code):

function display_category_level(parent := -1) :
    if children[parent] :
        print <ul>
        for category in children[parent] :
            print <li>
            print <h2>category[name]</h2>
            display_category_level(category[id])
            print </li>
        print </ul>

[kslakhani]

 

 

Start by fixing the database part: Make sure the query is correct, and then call mysql_fetch_assoc() repeatedly until you've fetched all rows into the $children array. You can check the array with var_dump().

for that i use 

$query = "SELECT cat_id, cat_name, parent_id, level FROM " .$DBPrefix. "categories ORDER BY cat_name";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
$items = mysql_fetch_assoc($res);
$html = '';
$parent = 0;
$parent_stack = array();

$children = array();
foreach ( $items as $item )
    $children[$items['parent_id']][] = $item;
  var_dump($children);

$children dosnt fetch all rows

gives me like

array(1) { [9]=> array(4) { [0]=> string(2) "10" [1]=> string(18) "40s, 50s & 60s" [2]=> string(1) "9" [3]=> string(1) "3" } }

Edited September 27, 2015 by kslakhani

[Jacques1]

Like I already said, you need to call the fetch function repeatedly:

$query = mysql_query(...);

$children = [];
while ($category = mysql_fetch_assoc($query))
{
    $children[$category['parent_id']] = $category;
}

Note that the mysql_* functions are horribly outdated and will be removed in the near future. Nowadays, we use modern database interfaces like PDO.

[kslakhani]

Thanks Jacques1,

 

 

$query = mysql_query(...);

$children = [];
while ($category = mysql_fetch_assoc($query))
{
$children[$category['parent_id']] = $category;
}

 

I have got working query now it fetches all arrays

Thanks for your suggestion regarding PDO as I am aware of but at the i am using an olld my_sql.* coz i need to change the my  entire queries. And will soon to jump on that

 

how do I implement 

 

 

function display_category_level(parent := -1) :
if children[parent] :
print <ul>
for category in children[parent] :
print <li>
print <h2>category[name]</h2>
display_category_level(category[id])
print </li>
print </ul>

 

Thanks

[Barand]

see Jaques1's pseudocode in #4 above

[kslakhani]

see Jaques1's pseudocode in #4 above

how do i write in PHP code

sorry, I am not that expert

[Barand]

here's an example

$sql = "SELECT category_id, name, parent FROM category";

$children = [];
$res = $db->query($sql);
while (list($cid, $name, $pid) = $res->fetch_row()) {
    $children[$pid][$cid] = $name;
}

display_category_level($children);

function display_category_level(&$children, $parent=-1)
{
    if (isset($children[$parent])) {
        echo "<ul>\n";
        foreach ($children[$parent] as $id=>$name) {
            echo "<li>$name</li>\n";
            display_category_level($children, $id);
        }
        echo "</ul>\n";
    }
    
}

[kslakhani]

@Barand

Sorry for the late reply as I was trying to make it with BS3. 

the above code works fantastic as plain html.

However I cant make it work with Bootstrap 3 dropdown menu, I want to make dropdown with all parent_id = 1(these are like master parents) like shown pic on very first post

my code is:

display_category_level($children);

function display_category_level(&$children, $parent=1)
{
 
echo '<div class="dropdown" > ';
echo '<button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
   
    <span class="caret"></span>categories</button>';
     
    if (isset($children[$parent])) {
  
   	
   	echo '<ul class="dropdown-menu "  role="menu" >',"\n";
       
        foreach ($children[$parent] as $id=>$name) {
            echo "<li>$name</li>\n";
           display_category_level($children, $id);
        }
        echo "</ul>\n";
    }
    
}

the problem is dropdown works on very first level but on parents category  its not working like the above pic shows, been playing with almost 2 days now

Any help will be much appreciated.

Edited September 29, 2015 by kslakhani

[Jacques1]

It's not valid to have an ul element as the child of another ul element. That's why I proposed putting the child-ul into the li element in my pseudo-code:

<ul>
    <li>
        <h2>List heading</h2>
        <!-- sub list -->
        <ul>
            <li>
                ...
            </li>
            <li>
                ...
            </li>
            ...
        </ul>
    </li>
    <li>
        ...
    </li>
</ul>

Edited September 29, 2015 by Jacques1

[kslakhani]

 

It's not valid to have an ul element as the child of another ul element. That's why I proposed putting the child-ul into the li element in my pseudo-code:

<ul>
    <li>
        <h2>List heading</h2>
        <!-- sub list -->
        <ul>
            <li>
                ...
            </li>
            <li>
                ...
            </li>
            ...
        </ul>
    </li>
    <li>
        ...
    </li>
</ul>

Hey Jaques1,

can you show me how do i do your pseudo-code in PHP?

will be much appreciated.

[Jacques1]

I suggest you try it for yourself. If you cannot possibly get it to work, show your attempts and explain the problem.

 

This is really just a small adjustment of the code you already have.

[kslakhani]

 

 

function display_category_level(parent := -1) :

if children[parent] :

print <ul>

for category in children[parent] :

print <li>

print <h2>category[name]</h2>

display_category_level(category[id])

print </li>

print </ul>

i dont understand 

for category in children[parent] :

[Jacques1]

In PHP, that would be a foreach loop. See Barand's code in reply #10:

foreach ($children[$parent] as $category_id => $category_name)
{
    ...
}

[kslakhani]

her is my code 

$children = [];

while (list($cid, $name, $pid) = mysql_fetch_row($res)) {
    $children[$pid][$cid] = $name;
}


display_category_level($children);


function display_category_level(&$children, $parent=1)
{
     
    if (isset($children[$parent])) {
  echo '<div class="dropdown">';
   	
          echo '<ul class="dropdown-menu "  role="menu" >',"\n";
        
       foreach ($children[$parent] as $id=>$name) {
     
         echo "<strong>$name</strong>";
      
           display_category_level($children, $id);
           
            echo "<li>". $name."</li>";
           
              echo "</ul>";
           
        }
          
       
    }
    echo '</div>';
}

i got result like

Ancient World

• Ancient World
• Books & Manuscripts

• Books & Manuscripts
• Cameras

• Cameras
• Ceramics & Glass
  Carnival
• Carnival
• Chalkware

• Chalkware
• Chintz & Shelley
• Chintz & Shelley
• Contemporary Glass
• Contemporary Glass
• Decorative
• Decorative
• Porcelain
• Porcelain
• Glass
• Ceramics & Glass
• Fine Art
• Fine Art
• General
• General
• Musical Instruments
• Musical Instruments
• Orientalia
• Orientalia
• Painting
• Painting
• Photographic Images
• Photographic Images
• Post-1900
• Post-1900
• Pre-1900
• Pre-1900
• Prints
• Prints
• Scientific Instruments
• Scientific Instruments
• Silver & Silver Plate..........................

tried it different ways but not getting it. I know I am nearly there but like I am unluckily not getting it

I want to display it like 

master_cat(main Dropdown) >

   main_cat1(drp) > parent_cat	parent_cat parent_cat	parent_cat	parent_cat
		     sub_cat	sub_cat	    sub_cat	sub_cat		sub_cat		
		     sub_cat	sub_cat			sub_cat         sub_cat
		     sub_cat	sub_cat		                        sub_cat
				
				parent_cat	parent_cat	parent_cat
				sub_cat		sub_cat		sub_cat		
				sub_cat		sub_cat		sub_cat
										
										
										
main_cat2(drp) >           parent_cat	parent_cat parent_cat	parent_cat	parent_cat             parent_cat
				sub_cat		sub_cat		sub_cat		sub_cat		sub_cat		
				sub_cat		sub_cat		sub_cat		sub_cat
				sub_cat		sub_cat		sub_cat
				sub_cat				sub_cat
				
				parent_cat	parent_cat	parent_cat
				sub_cat		sub_cat		sub_cat		
				sub_cat		sub_cat		sub_cat
				sub_cat					
				
ain_cat3(drp) >              parent_cat	     parent_cat     parent_cat	       parent_cat
				sub_cat		sub_cat		sub_cat		sub_cat			
				sub_cat		sub_cat		sub_cat		sub_cat
				sub_cat		sub_cat		sub_cat
				sub_cat					
				
				parent_cat	parent_cat	parent_cat
				sub_cat		sub_cat		sub_cat		
				sub_cat		sub_cat		sub_cat
				sub_cat				sub_cat
										
				

[kslakhani]

Hey Jacques1, I am nearly there

function display_category_level(&$children, $parent=1) {

    if (isset($children[$parent])) {
  
        echo '<ul>';
        
        foreach ($children[$parent] as $id=>$name) {
	          echo "<li><strong>$name</strong>";	          
	           echo "<ul>";
	            display_category_level($children, $id);
	           echo "<li>$name</li>";	        
		}
  	 echo '</ul>';
  	echo "</li>";
	 }
	echo '</ul>';
 }

only 1 problem is it repeats everything like

 

 

• Art & Antiques
  • Amateur Art
  • Amateur Art
  • Ancient World
  • Ancient World
  • Books & Manuscripts
  • Books & Manuscripts
  • Cameras
  • Cameras
  • Ceramics & Glass
    • Glass
      • Art Glass
      • Art Glass
      • Carnival
      • Carnival
      • Chalkware
      • Chalkware
      • Chintz & Shelley
      • Chintz & Shelley
      • Contemporary Glass
      • Contemporary Glass
      • Decorative
      • Decorative
      • Porcelain
      • Porcelain
    • Glass
  • Ceramics & Glass
  • Fine Art
  • Fine Art
  • General
  • General
  • Musical Instruments
  • Musical Instruments
  • Orientalia
  • Orientalia
  • Painting
  • Painting
  • Photographic Images
  • Photographic Images
  • Post-1900
  • Post-1900
  • Pre-1900
  • Pre-1900
  • Prints
  • Prints
  • Scientific Instruments
  • Scientific Instruments
  • Silver & Silver Plate
  • Silver & Silver Plate
  • Textiles & Linens
  • Textiles & Linens
• Art & Antiques
• Books
  • Animals
  • Animals
  • Arts, Architecture & Photography
  • Arts, Architecture & Photography
  • Audiobooks
  • Audiobooks
  • Biographies & Memoirs
  • Biographies & Memoirs
  • Business & Investing
  • Business & Investing
  • Catalogs
  • Catalogs
  • Children
  • Children
  • Computers & Internet
  • Computers & Internet
  • Contemporary
  • Contemporary
  • Cooking, Food & Wine
  • Cooking, Food & Wine
  • Entertainment
  • Entertainment
  • Foreign Language Instruction
  • Foreign Language Instruction
  • General

[Jacques1]

You echo $name twice, once in a strong element and then again in a li element.

 

The element structure also isn't valid HTML yet. The display_category_level() function already yields a ul element, so you mustn't put this into another ul element. Simple create a single li element on each iteration:

<?php

// test data
$children = [
    1 => [
        2 => 'Main 1',
        3 => 'Main 2',
    ],
    2 => [
        4 => 'Sub 1',
    ],
    3 => [
        5 => 'Sub 2'
    ],
    4 => [
        6 => 'Subsub 1',
        7 => 'Subsub 2',
    ]
];

display_category_level($children);



function display_category_level($children, $parent = 1)
{
    if (isset($children[$parent]))
    {
        echo '<ul>';
        foreach ($children[$parent] as $id => $name)
        {
            echo '<li>';
            echo '<h2>' . htmlspecialchars($name, ENT_QUOTES | ENT_SUBSTITUTE | ENT_HTML5, 'UTF-8') . '</h2>';
            display_category_level($children, $id);
            echo '</li>';

        }
        echo '</ul>';
    }
}

[kslakhani]

 

 

// test data

$children = [

1 => [

2 => 'Main 1',

3 => 'Main 2',

],

2 => [

4 => 'Sub 1',

],

3 => [

5 => 'Sub 2'

],

4 => [

6 => 'Subsub 1',

7 => 'Subsub 2',

]

];

 

Hey Jacques1,

categories are not serialized in DB even main categories have id like1,2,4,8,29, 75 etc etc. How do I assign keys in $children[]?? is it multi-dimentional array?? 

Sorry pal, again I am blank here

[Jacques1]

But you already have figured out the code for filling $children (as you demonstrated in reply #17), and you even showed us a complete example list.

 

So what's the problem? My test data was really just test data, it doesn't matter if the IDs are sequential or not. It was just easier for me to type.