Nested comments in PHP and MySQL

Question

I searched in the forums, but could not get an authoritative answer. I want to implement the structure of nested comments this way:

<ul> <li>This is the parent first comment! <ul> <li>This is the reply for the first parent comment! <ul> <li>This is a reply for the first reply of the parent comment!</li> <li>This is a third reply for the first parent comment!</li> </ul> </li> <li>This is another reply for first parent comment!</li> </ul> </li> <li>This is gonna be parent second comment! <ul> <li>This is a reply for the second comment!</li> </ul> </li> <li>This is fourth parent comment!</li> </ul> 

Dump for my table below:

 +----+------------------------------------------------------------+--------+ | id | text | parent | +----+------------------------------------------------------------+--------+ | 1 | This is the parent first comment! | 0 | | 2 | This is gonna be parent second comment! | 0 | | 3 | This is the reply for the first parent comment! | 1 | | 4 | This is another reply for first parent comment! | 1 | | 5 | This is a reply for the first reply of the parent comment! | 3 | | 6 | This is a reply for the second comment! | 2 | | 7 | This is a third reply for the first parent comment! | 3 | | 8 | This is fourth parent comment! | 0 | +----+------------------------------------------------------------+--------+ 

I know how to use mysql_query() and while() loops. New to PHP and MySQL. Please help me.

Answer 1

I did something similar for my blogpost. However, I just tried the same data. When you say nested comments , it is best to use nested functions as follows:

 function getComments($parent) { # Get the data from SQL. # Check if it has nested comments. if (hasComments()) getComments($child); # $child is the ID of the current comment. } 

To do this, I imported your data into MySQL and tried this:

 <?php mysql_connect('localhost', 'root'); mysql_select_db('nestedcomments'); function getComments($parent) { $res = mysql_query("SELECT * FROM `nestcomm` WHERE `parent` = $parent"); if (mysql_num_rows($res)) { echo "<ul>\n"; while (($dat = mysql_fetch_array($res)) !== false) echo "<li>", $dat["text"], getComments($dat["id"]), "</li>\n"; echo "</ul>\n"; } else echo ($parent === 0) ? 'No Comments!' : ""; } getComments(0); ?> 

As I said, I used nested functions, and, as you requested, the output is almost the same (without bindings) as follows:

 <ul> <li>This is the parent first comment!<ul> <li>This is the reply for the first parent comment!<ul> <li>This is a reply for the first reply of the parent comment!</li> <li>This is a third reply for the first parent comment!</li> </ul> </li> <li>This is another reply for first parent comment!</li> </ul> </li> <li>This is gonna be parent second comment!<ul> <li>This is a reply for the second comment!</li> </ul> </li> <li>This is fourth parent comment!</li> </ul> 

Hope this helps.

Answer 2

There are several different ways to store hierarchical data in MySQL. Check out the Bill Karwin presentation , which showcases four options.

• Contact list
• Enumeration path
• Nested Kits
• Closing table

---

You use the adjacency list model to store hierarchical data, but unfortunately this is the strongest model you could choose to query subtrees.

Your options:

• Change to another model.
• Limit queries to n levels.
• Use the stored procedure for a recursive query. For more information about this, see the Quassnoi series - Hierarchical Queries in MySQL .