mysql query - blog posts and comments with limit
I have 2 tables开发者_开发问答: comments & posts. I'd like to display a list of 15 posts and maximum 2 most recent comments under each blog post entry using mysql.
the database scheme looks like this
posts_table:
post_id, post_txt, post_timestamp
comments_table:
post_id, comment_txt, comment_timestamp
how should the mysql query look in order to select 15 posts and their related comments (max 2 most recent ones per post)?
thanks
MySQL LIMIT
SELECT * FROM posts_table LIMIT 0, 15
And to pull the most recent comments:
SELECT * FROM comments_table ORDER BY comment_timestamp DESC LIMIT 0, 2
I'll leave it to you to JOIN the two queries together somehow...
Firstly i would select the posts like so
$resource = mysql_query('SELECT * FROM posts LIMIT 0,10'); //your own query in place here to get posts
$posts = array();
while($row = mysql_fetch_assoc($resource))
{
$query = sprintf('SELECT * FROM comments WHERE post_id = %d',$row['post_id']);
$comments = mysql_query($query);
while($row2 = mysql_fetch_assoc($comments))
{
$row['comments'] = $row2;
}
$posts[] = $row;
}
Then in your template/view
foreach($posts as $post)
{
//Print out your main posts data here.
foreach($post['comments'] as $comment)
{
//Print out your comments here!
}
}
Hope this helps
精彩评论