tree menu from database - 3 levels

[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!!

[PeejAvery]

You need nested SQL statements

<?php
$menu = [];
$result1 = mysql_query("SELECT * FROM table WHERE parent_id = 0");
while ($row1 = mysql_fetch_assoc($result1)) {
  echo $row1['menu_title'];

  $result2 = mysql_query("SELECT * FROM table WHERE parent_id = " . $row1['category_id']);
  while ($row2 = mysql_fetch_assoc($result2)) {
    echo '...' . $row2['menu_title'];

    $result3 = mysql_query("SELECT * FROM table WHERE parent_id = " . $row2['category_id']);
    while ($row3 = mysql_fetch_assoc($result3)) {
      echo '......' . $row3['menu_title'];
    }
  }
}
?>

[laserlight]

What PeejAvery stated is one way to do this, but you might also consider the approach described in this article on Storing Hierarchical Data in a Database.