Building a html list tree from SQL table using PHP

Question

I have this table:

+-------------------------------+
| NodeID | Parent | HasChildren |
+-------------------------------+
|1000000 |-1      |-1           |
+--------+--------+-------------+
|2409999 |1000000 |-1           |
+-------------------------------+
|2510921 |1000000 |-1           |
+-------------------------------+
|2596822 |2510921 |0            |
+-------------------------------+
|3000143 |2409999 |0            |
+-------------------------------+
|3125674 |2409999 |0            |
................................
       the list goes on

... from which I need to build a html tree list using <ul> and <li>. Every node in this table is a child of top node with id 1000000 (which has Parent "-1"). Also, HasChildren "-1" tells that this node has children, 0 - it has not. Yes, it's kinda weird convention, but it's as it is. So, the output should be like this:

<ul>
  <li>2409999</li>
  <ul>
    <li>3000143</li>
    <li>3125674</li>
  </ul>
  <li>2510921</li>
  <ul>
    <li>2596822</li>
  </ul>
....
</ul>

Perhaps someone has tackled the very same problem? Any help would be appreciated. Thanks!

the numbers in your example don't match the ones in the table. Can you make an example with the table as given? — Bazzz
– Bazzz, Commented Nov 10, 2011 at 15:48
this example is for illustration only. i really can't make an example with the table as it's got over 20k entries. — Ghinzu
– Ghinzu, Commented Nov 10, 2011 at 15:50
@Ghinzu, Bazzz meant that the example data you have included at the top of the question do not match the data in the example output at the end of you question. Please can you edit them so that they match? — user359040
– user359040, Commented Nov 10, 2011 at 16:14
@MarkBannister Bazzz, Made an example. Thanks for your input guys! — Ghinzu
– Ghinzu, Commented Nov 10, 2011 at 17:00

Val Redchenko · Accepted Answer · 2011-11-10 16:17:55Z

3

As mentioned above, it would make sense to use a nested set representation in your table. If you decide to keep the existing table structure, then the general way of doing it is to have a recursive function along the lines of:

function printBranch($parentID) {
    foreach ($children as $child) {
        if ($child is a Leaf) echo '<li>child</li>';
        elseif ($child is a Branch) printBranch($child);
    }
}

Needless to say the code above is pseudo-code, but it should demonstrate the general idea. The function executes on a single node, and if that node has children it calls itself on each of the children. That is called recursion. As a common programming convention: nodes that have children are called branches, and the ones that don't are called leafs.

answered Nov 10, 2011 at 16:17

Val Redchenko

5811 gold badge8 silver badges21 bronze badges

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

Comments

Johan · Accepted Answer · 2011-11-10 16:07:47Z

0

If your table layout is up for redesign, I'd use a left-right table layout, like the one listed in this article.

http://www.sitepoint.com/hierarchical-data-database-2/

answered Nov 10, 2011 at 16:07

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Collectives™ on Stack Overflow

Building a html list tree from SQL table using PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related