Geek asks and answers

Mysql query - blog posts and restricted comments

I have 2 tables: comments and posts. I would like to display a list of 15 posts and a maximum of 2 last comments under each blog post using mysql.

The database schema is as follows:

posts_table: 
post_id, post_txt, post_timestamp

comments_table: 
post_id, comment_txt, comment_timestamp

What should a mysql query look like to select 15 posts and their associated comments (maximum 2 last per post)?

thank

+2
source share
2 answers

MySQL LIMIT

SELECT * FROM posts_table LIMIT 0, 15

And display the latest comments:

SELECT * FROM comments_table ORDER BY comment_timestamp DESC LIMIT 0, 2

I will leave this to you to join the two requests together ...

+1
source

Firstly, I would choose such messages

$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

-1

More articles:


All Articles