mysql query - blog posts and comments with limit

2
votes

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

2
phpmysqlgreatest-n-per-group

2 Answers

1
votes

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...

-1
votes

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