mysql query - blog posts and comments with limit

Question

Hi,

I have 2 tables: comments and posts and I'd like to display a list of 15 posts and maximum 2 most recent comments under each blog post

the database scheme looks like this

posts_table: post_id, post_txt, post_timestamp comments_table: post_id, comment_txt, comment_timestamp

how the mysql query should look like to select 15 posts and related comments (max 2 most recent ones per post) ???

thanks, Leo

Answer 1

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

Answer 2

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