I have a table with hierarchical-data (comments with replies) and I wonder how I can sort it so that the newest comments will be prepended as the first ones and the newest replies for each comment should be appended as the last ones.
An example of the table
Here is an example of what it looks like:
id | path | reply_to | date
1 1 NULL 8:00
2 1-2 1 9:00
3 1-3 1 10:00
4 4 NULL 11:00
5 1-5 1 12:00
6 4-6 4 13:00
7 4-7 4 14:00
If I want to select the path of my result, it should be ordered like this:
4
4-6
4-7
1
1-2
1-3
1-5
Is this possible using MySQL queries?
What I have been doing until now
Until now, I have only selected the rows with no replies ordered by ID DESC
, and then (for each row) I requested its replies and ordered them by ID ASC
, I used PDO in PHP to connect to my database:
$stmt1 = $this->db->query("SELECT * FROM comments WHERE reply_to IS NULL ORDER BY id DESC");
$stmt2 = $this->db->prepare("SELECT * FROM comments WHERE reply_to = ? ORDER BY id ASC");
while($row = $stmt1->fetch()) {
//display the comment
$stmt2->execute(array($row['id']));
while($row2 = $stmt2->fetch()) {
//display all its replies
}
}
But I think if I query only one time it would be much better than querying inside of a loop like I am doing now, right?
I hope my question is understandable. Unfortunately, I have no idea how to solve this problem. I thought that SQL Joins are the solution, but as far as I know they are "used to combine rows from two or more tables, based on a common field between them".
Here is an SQL Fiddle to play around.
Thanks in advance.