How to get all posts with all comments using 2 SQL queries

Question

I am trying to make a page with a list of posts, and under each post are all comments that belong to this post. Initially, I wanted to use only one query to retrieve all posts + comments using SQL JOIN, but I find it impossible, for example, to get a message with several comments. It displays only posts with a maximum of 1 comment per post, or simply displays the message several times depending on the number of comments.

In this related question, someone was talking about using 2 queries: How to print posts and comments with only one sql query

But how do I do this?

I have a query and while loop for posts, but I obviously don't want to run a query for comments for each post inside this loop.

$getPost = mysql_query('SELECT p.post_id, p.user_id, p.username, p.content FROM post p ORDER BY p.post_id DESC'); while($row = mysql_fetch_array($getPost)) { ... } 

Table structure (the answer is a table for storing comments):

 POST (post_id (primary key), user_id, username, content, timestamp) REPLY (reply_id (primary key), post_id, username, reply_content, timestamp) 

Answer 1

You can do this in one request, which is fine if the amount of data in your original posts is small :

 $getPost = mysql_query('SELECT p.*, r.reply_id, r.username r_username, r.reply_content, r.timestamp r_timestamp FROM post p left join reply r ORDER BY p.post_id DESC' ); $posts = array(); $last_id = 0; while($row = mysql_fetch_array($getPost)) { if ($last_id != $row['post_id']) { $posts[] = array( 'post_id' => $row['post_id'], 'user_id' => $row['user_id'], 'username' => $row['username'], 'content' => $row['content'], 'timestamp' => $row['timestamp'], 'comments' => array() ); } $posts[sizeof($posts) - 1]['comments'][] = array( 'reply_id' => $row['reply_id'], 'username' => $row['r_username'], 'reply_content' => $row['reply_content'], 'timestamp' = $row['r_timestamp'] ); } 

Otherwise, divide it into two queries:

 $getPost = mysql_query('SELECT p.*, FROM post p ORDER BY p.post_id DESC' ); $rows = array(); $ids = array(); $index = array(); while($row = mysql_fetch_assoc($getPost)) { $row['comments'] = array(); $rows[] = $row; $ids[] = $row['post_id']; $index[$row['post_id']] = sizeof($rows) - 1; } $getComments = mysql_query('select r.* from replies r where r.post_id in ("' . join('","', $ids) . '")'); while ($row = mysq_fetch_assoc($getComments)) { $rows[$index[$row['post_id']]]['comments'][] = $row; } 

... Or something like that. Any option allows you to clog your first request with WHERE clauses, etc. For your heartfelt content. The advantage of the second approach is that you do not retransmit your original data for each comment!

Answer 2

To also receive these messages without comment, you need to use LEFT OUTER JOIN . In this case, any row in the first table without any corresponding rows in the second table will be conjugated to a row consisting of zero values.

 SELECT * FROM posts LEFT OUTER JOIN comments ON posts~post_id = comments~post_id; 

By the way: there is also a RIGHT OUTER JOIN . When you use this, you will receive all comments, including those where the parent post was somehow lost, but there are no posts without comments.