PHP如何循环多个注释级别

Question

My database structure currently looks like this (for a commenting system):

However, I'm completely lost on how to loop through such data, especially with the nested comments/different levels.

Currently, I loop through the data and display all level 0 comments, and for each level 0 comment, loop through and find it's corresponding level 1 comment. However, there can be a maximum of 10 levels, and looping through for all 10 the way I am, I feel would be very inefficient.

The replyTo column refers to the ID of the comment it is a reply to. If the number is 0, it means the comment is not a reply. So for the table above, for example, I would want to loop through it with PHP to display something as so (from most recent to oldest):

hello
    what do you want
    test
        Good!
test
This is a page!
    test

Answer 1

Example php script (assuming a database connection class passed to the function).

<?php

post_replies($db, 0, 0);

function post_replies($db, $cnt=0, $parent_id=0)
{
    $messages = array();
    $sql = "SELECT id, message
            FROM some_table
            WHERE replyTo = ".(int)$parent_id."
            ORDER BY date";
    $results = $db->query($sql);
    while($row = $db->fetch_assoc())
    {
        $messages[] = $row;
    }
    foreach($messages AS $message)
    {
        echo str_repeat("\t", $cnt).$message['message'];
        post_replies($db, $cnt + 1, $message['id'])
    }
}

?>

This could be tweaked a touch to avoid doing a further query for a message that has no replies:-

<?php

post_replies($db, 0, 0);

function post_replies($db, $cnt=0, $parent_id=0)
{
    $messages = array();
    $sql = "SELECT a.id, a.message, COUNT(b.id) AS child_count
            FROM some_table a
            LEFT OUTER JOIN some_table b
            ON a.id = b.parent_id
            WHERE a.replyTo = ".(int)$parent_id."
            GROUP BY a.id, a.message
            ORDER BY a.date";
    $results = $db->query($sql);
    while($row = $db->fetch_assoc())
    {
        $messages[] = $row;
    }
    foreach($messages AS $message)
    {
        echo str_repeat("\t", $cnt).$message['message'];
        if ($message['child_count'] > 0)
        {
            post_replies($db, $cnt + 1, $message['id'])
        }
    }
}

?>

EDIT

Had a think about this, and decided to have a play. As you have a max number of levels this might be possible with a verbose bit of SQL.

This works by getting all the messages with a replyTo of 0, then UNIONing that with all those with a replyTo of 0 AND child record. and doing the same with a grand child record, etc.

With a bit of a fudge on the sort this brings things back in the right order:-

SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        b3.date AS b3Date, b3.id AS b3Id, 
        b4.date AS b4Date, b4.id AS b4Id, 
        b5.date AS b5Date, b5.id AS b5Id, 
        b6.date AS b6Date, b6.id AS b6Id, 
        b7.date AS b7Date, b7.id AS b7Id, 
        b8.date AS b8Date, b8.id AS b8Id, 
        CONCAT(REPEAT('-', 9), b8.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
INNER JOIN some_table b3 ON b2.id = b3.replyTo
INNER JOIN some_table b4 ON b3.id = b4.replyTo
INNER JOIN some_table b5 ON b4.id = b5.replyTo
INNER JOIN some_table b6 ON b5.id = b6.replyTo
INNER JOIN some_table b7 ON b6.id = b7.replyTo
INNER JOIN some_table b8 ON b7.id = b8.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        b3.date AS b3Date, b3.id AS b3Id, 
        b4.date AS b4Date, b4.id AS b4Id, 
        b5.date AS b5Date, b5.id AS b5Id, 
        b6.date AS b6Date, b6.id AS b6Id, 
        b7.date AS b7Date, b7.id AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 8), b7.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
INNER JOIN some_table b3 ON b2.id = b3.replyTo
INNER JOIN some_table b4 ON b3.id = b4.replyTo
INNER JOIN some_table b5 ON b4.id = b5.replyTo
INNER JOIN some_table b6 ON b5.id = b6.replyTo
INNER JOIN some_table b7 ON b6.id = b7.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        b3.date AS b3Date, b3.id AS b3Id, 
        b4.date AS b4Date, b4.id AS b4Id, 
        b5.date AS b5Date, b5.id AS b5Id, 
        b6.date AS b6Date, b6.id AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 7), b6.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
INNER JOIN some_table b3 ON b2.id = b3.replyTo
INNER JOIN some_table b4 ON b3.id = b4.replyTo
INNER JOIN some_table b5 ON b4.id = b5.replyTo
INNER JOIN some_table b6 ON b5.id = b6.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        b3.date AS b3Date, b3.id AS b3Id, 
        b4.date AS b4Date, b4.id AS b4Id, 
        b5.date AS b5Date, b5.id AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 6), b5.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
INNER JOIN some_table b3 ON b2.id = b3.replyTo
INNER JOIN some_table b4 ON b3.id = b4.replyTo
INNER JOIN some_table b5 ON b4.id = b5.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        b3.date AS b3Date, b3.id AS b3Id, 
        b4.date AS b4Date, b4.id AS b4Id, 
        NULL AS b5Date, NULL AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 5), b4.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
INNER JOIN some_table b3 ON b2.id = b3.replyTo
INNER JOIN some_table b4 ON b3.id = b4.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        b3.date AS b3Date, b3.id AS b3Id, 
        NULL AS b4Date, NULL AS b4Id, 
        NULL AS b5Date, NULL AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 4), b3.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
INNER JOIN some_table b3 ON b2.id = b3.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        b2.date AS b2Date, b2.id AS b2Id, 
        NULL AS b3Date, NULL AS b3Id, 
        NULL AS b4Date, NULL AS b4Id, 
        NULL AS b5Date, NULL AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 3), b2.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
INNER JOIN some_table b2 ON b1.id = b2.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        b1.date AS b1Date, b1.id AS b1Id, 
        NULL AS b2Date, NULL AS b2Id, 
        NULL AS b3Date, NULL AS b3Id, 
        NULL AS b4Date, NULL AS b4Id, 
        NULL AS b5Date, NULL AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 2), b1.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
INNER JOIN some_table b1 ON b0.id = b1.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        b0.date AS b0Date, b0.id AS b0Id, 
        NULL AS b1Date, NULL AS b1Id, 
        NULL AS b2Date, NULL AS b2Id, 
        NULL AS b3Date, NULL AS b3Id, 
        NULL AS b4Date, NULL AS b4Id, 
        NULL AS b5Date, NULL AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        CONCAT(REPEAT('-', 1), b0.message)
FROM some_table a
INNER JOIN some_table b0 ON a.id = b0.replyTo
WHERE a.replyTo = 0
UNION ALL
SELECT a.date AS aDate, a.id AS aId, 
        NULL AS b0Date, NULL AS b0Id, 
        NULL AS b1Date, NULL AS b1Id, 
        NULL AS b2Date, NULL AS b2Id, 
        NULL AS b3Date, NULL AS b3Id, 
        NULL AS b4Date, NULL AS b4Id, 
        NULL AS b5Date, NULL AS b5Id, 
        NULL AS b6Date, NULL AS b6Id, 
        NULL AS b7Date, NULL AS b7Id, 
        NULL AS b8Date, NULL AS b8Id, 
        a.message
FROM some_table a
WHERE a.replyTo = 0
ORDER BY aDate DESC, aID, 
        IFNULL(b0Date, '2099-12-31') DESC, b0Id, 
        IFNULL(b1Date, '2099-12-31') DESC, b1Id, 
        IFNULL(b2Date, '2099-12-31') DESC, b2Id, 
        IFNULL(b3Date, '2099-12-31') DESC, b3Id, 
        IFNULL(b4Date, '2099-12-31') DESC, b4Id, 
        IFNULL(b5Date, '2099-12-31') DESC, b5Id, 
        IFNULL(b6Date, '2099-12-31') DESC, b6Id, 
        IFNULL(b7Date, '2099-12-31') DESC, b7Id, 
        IFNULL(b8Date, '2099-12-31') DESC, b8Id

SQL fiddle for it:-

http://www.sqlfiddle.com/#!2/775405/12