Geek Answers Handbook

How to print posts and comments with only one sql query

Is it possible to print (PHP) all my blog posts + related comments through a single sql query?

If so, how?

I thought 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 it did not work as I expected

+1
source share
6 answers

Using a single SQL query is not very convenient, since you have 1 post and several comments.
Having information about posts added to each comment (in a combined request) is a waste of resources.

It is much more convenient to get the details of the message and use the post_id message to find the comments that belong to the message.

+4
source

The easiest way I can think of is to iterate over all returned strings and group them into an associative array, where the keys are message identifiers. You can then iterate over this associative array and print comments for each message, taking the message header from the first line in the group.

0
source

When retrieving data, simply use the field name, for example: $ 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

0
source

If you are using MySQL, you can 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
0
source

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
0
source

"But it did not work as I expected."

... but you do not say what you expected.

Assuming the implied schema in your request is correct, then you don’t just need to show the messages 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 means that your request is correct (i.e. these comments are not hierarchical). A.

WITH.

0
source

More articles:


All Articles