7

I searched through the forums but couldn't get an authoritative answer. I want to implement a nested comment structure in a way like this:

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

The dump for my table is 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. A beginner in PHP & MySQL. Please help me out.

Improve this question
1

2 Answers 2

Reset to default
15

There are several different ways to store heirarchical data in MySQL. Check out Bill Karwin's presentation that demonstrates four options.

  • Adjacency List
  • Path Enumeration
  • Nested Sets
  • Closure Table

You are using the adjacency list model for storing heirarchical data, but unfortunately this is the hardest model you could choose for querying subtrees.

nested sets query subtree

Your options are:

  • Change to a different model.
  • Restrict queries to n levels deep.
  • Use a stored procedure to query recursively. For more information about this, see Quassnoi's series of articles - Hierarchical queries in MySQL.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Is the Adjacency list you said related to any graph models?
Yes. The OPs structure can be regarded as a tree (a graph without cycles).
Change the model as in? Are you talking about the database table structure?
@Aishu: Yes. With any of the models apart from the one you have chosen, your task would be easy.
4

I had done something similar for my blogpost. Yet, I just tried out with the same data. When you say nested comments it is better you can use nested functions this way:

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 make it this way, I have imported your data to 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 before I have used nested functions, and as you asked the output is almost same (without the braces) 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>

Hope this helps out.

Improve this answer

13 Comments

Looks like this is what I want. Let me implement and check.
Note that while this will give the correct result, it fires off one query per comment, so it will be slow. But if you're just doing this as an experiment, or for a site with few users and comments, it might be just fine for you.
Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information.
@PraveenKumar: PDO is the newer one, and it is included by default since 5.1.
@PraveenKumar: It's easier to convert from mysql to mysqli, so for old projects, do so. Learn PDO and use it in newer projects.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.