1

This is what my Database looks like

comments

id 1 - (primary key)
user_id 2
comment - I need help

id 2 - (primary key)
user_id 
comment - I'm going to sleep now

id 3 - (primary key)
user_id 2
comment - Watching the news.

Replies

id 1
comment_id 1 (foreign key)
reply - what do you need help with?

id 2
comment_id 1 (foreign key)
reply - i can help you!

id 3
comment_id 1 (foreign key)
reply - i don't understand!

id 4
comment_id 2 (foreign key)
reply - OK have a good night.

The issue is, i want the comments to display nested like the following:

comment - I need help

--- reply - what do you need help with?
---- i can help you!
----- i don't understand!

comment - I'm going to sleep now

--- reply - OK have a good night.

comment - Watching the news.

--- no reply

Not like this, which is what i'm getting at the moment.

comment - I need help

--- reply - what do you need help with?

comment - I need help

--- i can help you!

comment - I need help

---- i don't understand!

comment - I'm going to sleep now

--- reply - OK have a good night.

comment - Watching the news.

--- no reply

This is my Query

$query = $db->prepare("SELECT 

comments.id, 
comments.user_id,
comments.comment, 

replies.id,
replies.comment_id,
replies.reply

FROM comments
LEFT JOIN replies 
ON comments.id = replies.comment_id
WHERE comments.user_id = ? ORDER BY comments.id DESC");

try {
$query->execute();              

while($row = $query->fetch(PDO::FETCH_ASSOC)) {

$comm_id = $row['id'];
$comm_user_id = $row['user_id']; 
$comm = $row['comment'];

$re_id = $row['id']; 
$re_comm_id = $row['comment_id'];
$re_reply = $row['reply'];

echo $comm." - ".$re_reply;

}

} catch (PDOException $e) {
echo $e->getMessage();
exit();
}

The issue is obviously in the query structure, but i'm not sure how to go about structuring it so i can get the desired result.

I hope i explained my self clearly enough, Thanks for any help in advance.

Benjamin
  • 45
  • 8

1 Answers1

0

First change the query so that replies.id has an alias so we can reference later:

SELECT comments.id, comments.user_id, comments.comment, 
  replies.id reply_id, replies.comment_id, replies.reply
FROM comments
LEFT JOIN replies 
ON comments.id = replies.comment_id
WHERE comments.user_id = ? ORDER BY comments.id DESC

You can group the replies by comment_id first.

<?php
$comments = array();
$comm_replies = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)) {

    $comm_id = $row['id'];
    $comm_user_id = $row['user_id']; 
    $comm = $row['comment'];

    if (empty($row['reply_id'])) {
      continue;    
    }

    $comments[$comm_id] = $comm;
    $comm_replies[$comm_id][] = $row;
}

foreach ($comments as $comm_id => $comm) {
    echo "comment - $comm\n";
    if (!isset($comm_replies[$comm_id])) {
        continue;
    }

    $prefix = '---';
    foreach ($comm_replies[$comm_id] as $reply_id => $row) {
        echo "$prefix $row['reply_id'], $row['reply']\n";
        $prefix .= '-';
    }
}
Fabricator
  • 12,722
  • 2
  • 27
  • 40
  • quick question @Fabricator.... if i wanted to display the id and comment_id along with the reply how would i go about that? – Benjamin Jun 17 '14 at 03:50
  • Ok! now we are heading down the right direction, but I want to display all the information from the replies table and lets say I added 2 more rows to the table with the name and image of the person who replied. How can I get all the info from both tables? Thanks. – Benjamin Jun 17 '14 at 16:49
  • @Benjamin, in that case, I would put the `$row` into `$comm_replies`, see the updated answer – Fabricator Jun 17 '14 at 16:56
  • i tweaked it a little i changed this `$comments[$comm_id] = $comm;` to this `$comments[$comm_id] = $row;` and changed the foreach loop from this `foreach ($comments as $comm_id => $comm) { echo "comment - $comm\n"; }` to this `foreach ($comments as $comm_id => $row) { echo "comment - $row[comment]\n"; }` ...thanks for all your help @Fabricator i really appreciate it – Benjamin Jun 18 '14 at 01:30
  • I know that you pretty much answered my question but i have been trying to limit the $comm_replies[$comm_id] so that it will only show 5 replies to each comment but once again i have gotten stuck i tried LEFT JOIN (SELECT * FROM replies LIMIT 5)replies ON comments.id = replies.comment_id..... thinking it would just work, but it just shows me the first comment with the first 5 rows from that comment only and not the rest of the comments with replies... my skills are limited and i'm not sure where to go from here... can you please help me once again – Benjamin Jul 08 '14 at 00:23
  • It's difficult to write an efficient query in mysql, but it is [possible](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/). – Fabricator Jul 08 '14 at 00:49
  • can you help me by showing me how it's done...like you did with the query. Thanks – Benjamin Jul 10 '14 at 11:24