CODEWITHSUNDEEP
phpmysqljoin
Bundy
Bundy

Reputation: 311

How to print posts and comments with only one sql query

Is it possible to print out (PHP) all my blog posts + associated comments via one sql query?

If so, how?

I was thinking in this direction:

SELECT p.post_id, p.title, c.comment_body
FROM posts p
LEFT JOIN comments c
ON c.parent_id = p.post_id

But this didn't work out as I expected

Upvotes: 1

Views: 2536

Answers (6)

Veger
Veger

Reputation: 37915

Using one SQL query is not very convenient, since you have 1 post and multiple comments.
Having the post details added to each comment (in a combined query) is a waste of resources.

It is much more convenient to get the post details and use post_id of the post to find the comments belonging to the post.

Upvotes: 4

symcbean
symcbean

Reputation: 48367

"But this didn't work out as I expected "

...but you don't say what you did expect.

Assuming that the the implied schema in your query is correct, then its a no brainer to only show the posts once:

$lastpostid=false;
while ($r=mysql_fetch_assoc($result)) {
  if ($r['post_id']!=$lastpost) {
     print "Post: " . $r['title'] . "<br />\n";
     $comment_id=1;
     $lastpost=$r['post_id'];
  }
  print "Comment # $comment_id : " . $r['comment_body'] . "<br />\n";
  $comment_id++;
}

But as I said this implies that your query is correct (i.e. that comments are not hierarchical).

C.

Upvotes: 0

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6736

For MySQL:

SELECT p.post_id, p.title, GROUP_CONCAT(c.comment_body), count(*) as coment_cnt
FROM
    posts p
        LEFT JOIN comments c ON (p.post_id = c.parent_id)
GROUP BY
    p.post_id

Upvotes: 0

c0deaddict
c0deaddict

Reputation: 161

If you're using MySQL you could use the GROUP_CONCAT function:

SELECT p.post_id, p.title, GROUP_CONCAT(c.comment_body)
FROM posts
LEFT JOIN comments c ON c.parent_id = p.post_id
GROUP BY p.post_id

Upvotes: 1

Scott
Scott

Reputation: 3485

When getting data just use the field name like: $result = mysql_query("SELECT p.post_id, p.title, c.comment_body FROM posts p LEFT JOIN comments c ON c.parent_id = p.post_id");

while($row = mysql_fetch_array($result))
  {
  echo $row['title'] . " " . $row['comment_body'];
  echo "<br />";
  }

From: http://www.tizag.com/mysqlTutorial/mysqljoins.php

Upvotes: 0

Ignas R
Ignas R

Reputation: 3409

The easiest way I can think of is to iterate through all the rows returned and group them into an associative array where keys are the post IDs. Then you can iterate through that associative array and print the comments for each post, taking the post title from the first row in the group.

Upvotes: 1

Related Questions