Can this be done in one SQL?

[GuitarGod]

Hi,

 

I wasn't sure whether to post this in the MySQL forum or not as it's part of a PHP script. My apologies if this is posted in the wrong forum. I'll try to explain this as best I can, and I hope you can help

 

I have a "comments" table. In this table, there is a "ID" and a "parent_comment" field - if the parent_comment field is set to 0, it is a main comment on the page, if it has another value, it's a reply to another comment.

 

Example:

 

ID: 1 | parent_comment: 0 | comment: message_here

ID: 2 | parent_comment: 1 | comment: another_message

ID: 3 | parent_comment: 2 | comment: another_message

 

The second row, with a parent_comment of 1, is in reply to the first comment, and the last comment is in reply to the second comment - bare in mind there could be an unlimited number of replies to replies.

 

What I have so far is something like

 

<?php

$sql = '??'; // This will select comments with a parent ID of 0

while (?? )
{
   $sql = '??'; // This will select replies to that comment

    while (??)
    {
        // Another SQL to get comments with a parent ID of 2
    }
}

?>

 

As you can see, I have 2 SQLs and while loops, but I really need a system to get an infinite number of replies. Is there a way to do this - perhaps even in just 1 SQL statement?

 

Thanks!

[scootstah]

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