PHP - MySQL create a tree view from product table

Question

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

thanks for your reply .. can you give me a little more explanation ? — ramesh
– ramesh, Commented Jun 3, 2013 at 6:50
Check stackoverflow.com/questions/16545580/multi-level-menu-with-php/… for the same type of tree from the same structure — Hugo Delsing
– Hugo Delsing, Commented Jun 3, 2013 at 6:53

BlitZ · Accepted Answer · 2013-06-03 06:55:05Z

2

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

answered Jun 3, 2013 at 6:55

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BlitZ · Accepted Answer · 2013-06-03 07:16:08Z

0

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

edited Jun 3, 2013 at 7:16

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

answered Jun 3, 2013 at 7:01

Vinoth Babu

6,82510 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

PHP - MySQL create a tree view from product table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related