Tree Menu From Database - 3 Levels (2 Records On First Level)

[wasssu]

Hi,

 

I want to build a tree menu (table with categories on 3 level) from database.

 

I want to look like this using unsorted lists (3 levels):

Category1

-Subcategory 1

-Subcategory 2

-Sub-Subcategory 1

-Sub-Subcategory 1

 

Category2

-Subcategory 1

-Subcategory 2

-Subcategory 3

-Sub-Subcategory 1

-Sub-Subcategory 1

-Subcategory 4

 

 

The structure of the Category table is like this:

category_id

parent_id

 

So i put from database into an array():

$menu = Array();
while ($m = mysql_fetch_array($result)) {
$menu[] = Array('id'=>$m['category_id'], 'parent'=>$m['parent_id']);
}

 

 

But from here i've tried different algorithms but it does't work.

 

 

I have only 2 main categories:

category_id = 207

parent_is = 0

 

category_id = 286

parent_is = 0

 

And then sub categories and sub subcategories

 

Could you please help me with this?

 

Thank you!!

[jcbones]

You need to line up the data, when it comes from the database, correctly. I would start with ordering it sorted by the parent ascending. Then I would sort that into an array, matching the id's to the parent. Finally, I would go through that array, and build the menu.

[wasssu]

You need to line up the data, when it comes from the database, correctly. I would start with ordering it sorted by the parent ascending. Then I would sort that into an array, matching the id's to the parent. Finally, I would go through that array, and build the menu.

 

I've found this but i don't know why is not working. It seems that i'm not building correctly the arrays.

Whe i'm using the commented code for #menu is working:

 

$result = mysql_query("SELECT * FROM category") or die(mysql_error());


while ($m = mysql_fetch_array($result)) {
$menu1[] = Array('id'=>$m['category_id'], 'parent'=>$m['parent_id']);
}
$menu = Array($menu1);


/*$menu = Array( // Presumed to have been coming from a SQL SELECT, populated for demo.
 Array('id'=>1,'title'=>'Menu 1',          'parent_id'=>null),
 Array('id'=>2,'title'=>'Sub 1.1',         'parent_id'=>1),
 Array('id'=>3,'title'=>'Sub 1.2',         'parent_id'=>1),
 Array('id'=>4,'title'=>'Sub 1.3',         'parent_id'=>1),
 Array('id'=>5,'title'=>'Menu 2',          'parent_id'=>null),
 Array('id'=>6,'title'=>'Sub 2.1',         'parent_id'=>5),
 Array('id'=>7,'title'=>'Sub Sub 2.1.1',   'parent_id'=>6),
 Array('id'=>8,'title'=>'Sub 2.2',         'parent_id'=>5),
 Array('id'=>9,'title'=>'Menu 3',          'parent_id'=>null),
);*/

function has_children($rows,$id) {
 foreach ($rows as $row) {
   if ($row['parent_id'] == $id)
     return true;
 }
 return false;
}
function build_menu($rows,$parent=0)
{  
 $result = "<ul>";
 foreach ($rows as $row)
 {
   if ($row['parent_id'] == $parent){
     $result.= "<li>{$row[title]}";
     if (has_children($rows,$row['id']))
       $result.= build_menu($rows,$row['id']);
     $result.= "</li>";
   }
 }
 $result.= "</ul>";

 return $result;
}
echo build_menu($menu);


?>

[Barand]

It's easier to build a 2D array with parent as the key. This should get you going.

 

<?php
include("testDBconnect.php");

$sql = "SELECT category_id, name, parent FROM category";
$res = $mysqli->query($sql);
$cats = array();
while ($row = $res->fetch_assoc()) {
   extract ($row);
   $cats[$parent][] = array ('id'=>$category_id, 'name'=>$name);
}

listCats($cats, 0);		 // call recursive list function


function listCats(&$cats, $parent, $level=0)
{
if (isset($cats[$parent]))
foreach ($cats[$parent] as $subcat) {
 $indent = str_repeat('----', $level);
 echo "$indent{$subcat['name']}<br />";
 listCats($cats, $subcat['id'], $level+1);
}
}
?>

Edited September 29, 2012 by Barand

[jazzman1]

Check this out -> http://www.sitepoint.com/hierarchical-data-database/