I have a category table as follows. Which I am saving category, subcategories in that table ( Multiple sub-categories ). My table looks like
id | name | parent_of | created_on
-------+---------------+---------------+---------------------
1 | Name 1 | 0 | 2013-05-1 00:00:00
-------+---------------+---------------+---------------------
2 | Name 2 | 0 | 2013-05-1 00:00:00
-------+---------------+---------------+---------------------
3 | Name 3 | 1 | 2013-05-1 00:00:00
-------+---------------+---------------+---------------------
4 | Name 4 | 1 | 2013-05-1 00:00:00
-------+---------------+---------------+---------------------
5 | Name 5 | 3 | 2013-05-1 00:00:00
-------+---------------+---------------+---------------------
6 | Name 6 | 3 | 2013-05-1 00:00:00
-------+---------------+---------------+---------------------
Now I need to query this table and create a tree structure as follows to make a easy navigation through categories in front end.
A tree like follows
1
|--> 3
| |--> 5
| |--> 6
|--> 4
2
I am bit confused about the query. Can anyone please help me ?
Thanks in advance
For basic display, you may use:
SELECT * FROM `Table` WHERE `parent_of` = 0;
It will give you all the root nodes.
When user click on a node, he / she / it submits request to the server (Ajax maybe) to fetch child nodes for clicked one, like:
$node = abs((int)$_GET['node']);
then:
SELECT * FROM `Table` WHERE `parent_of` = {$node};
Please try the below code logic for your solution,
$result = mysql_query("SELECT id, parent_of FROM category WHERE parent_of > 0");
$treeStructure = array();
while($row = mysql_fetch_array($result)) {
$id = $row['id'];
$parentID = $row['parent_of'];
$treeStructure[$parentID][] = $treeStructure[$id];
}