Nested comments in PHP & MySQL

Question

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.

You can see my example https://github.com/shahovkit/nested-comments — Nikita Shahov, Feb 21 '22 at 08:36

score 16 · Answer 1 · answered Jul 28 '12 at 10:29

16

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.

answered Jul 28 '12 at 10:29

Mark Byers

811,555
193
1,581
1,452

Is the Adjacency list you said related to any graph models? – Praveen Kumar Purushothaman Jul 28 '12 at 10:32
Yes. The OPs structure can be regarded as a tree (a graph without cycles). – Mark Byers Jul 28 '12 at 10:35
Change the model as in? Are you talking about the database table structure? – Jul 28 '12 at 10:36
@Aishu: Yes. With any of the models apart from the one you have chosen, your task would be easy. – Mark Byers Jul 28 '12 at 10:38

Praveen Kumar Purushothaman · Accepted Answer · 2012-07-30T14:25:16.400

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.

edited Jul 30 '12 at 14:25

answered Jul 28 '12 at 10:31

Praveen Kumar Purushothaman

164,888
24
203
252

Looks like this is what I want. Let me implement and check. – Jul 28 '12 at 10:35
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. – Mark Byers Jul 28 '12 at 10:37
Just copied the whole PHP Function. It works. Can you make it possible to add the spaces too? Is it really possible to count to that level by level? – Jul 28 '12 at 10:37
@MarkByers I just tried an experiment to find what the OP wants. Yeah, even I know this is not the right model for a nested comment. – Praveen Kumar Purushothaman Jul 28 '12 at 10:39
@MarkByers It is just for a simple one, just for my class to comment and leave replies. More to test! :) And yeah, this works perfectly for my use case. Thanks. – Jul 28 '12 at 10:41
@Aishu What Mark Byers said is right. Even I would say that it is better to change the structure of the database. Check out Wordpress's comment style by downloading it. You might be able to learn new stuff. :) – Praveen Kumar Purushothaman Jul 28 '12 at 10:44
4

Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). – Madara's Ghost Jul 28 '12 at 11:01
@Truth From which version of PHP, this has started? – Praveen Kumar Purushothaman Jul 28 '12 at 11:02
1

@PraveenKumar: PDO is the newer one, and it is **included by default** since 5.1. – Madara's Ghost Jul 28 '12 at 11:05
@Truth I asked about the deprecation status of `mysql_*` functions. Good thing I saw yours because, I have done a hundreds of apps, all which depend on `mysql_*` functions extensively. Is there a best way to convert them into PDO? And, can I use `mysqli_*` functions instead of `mysql_*` functions? Are both same? My database is gonna be MySQL. – Praveen Kumar Purushothaman Jul 28 '12 at 11:22
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. – Madara's Ghost Jul 28 '12 at 12:00
@uzyn This was the meant syntax! And the script outputs this way! – Praveen Kumar Purushothaman Jul 30 '12 at 14:26
**Beware**: This solution executes multiple queries against your database. Recursively running queries in a for/while loop is considered as bad practice. This may really hurt your overall system/site performance when your data grows. – edigu Mar 12 '17 at 19:05

Nested comments in PHP & MySQL

2 Answers2

Linked

Related