Jump to content

help, php drop down menu using hierarchical database


kslakhani

Recommended Posts

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

temp.png

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

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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

 

 

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

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

temp2.png

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

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

 

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.

Link to comment
Share on other sites

 

 

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

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

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

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

Link to comment
Share on other sites

 

 

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

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.