MySQL Selecting records by multiple IDs with LIMIT

Question

I'm stuck with my MySQLi query. The scenario is like this: - I need to select max 10 comments for each post. - I'm using this query but it doesn't work the way I need - Post IDs are in array

$comments_query = mysqli_query($con, "SELECT * FROM comments WHERE pid IN ({$decodedPostIds}) AND state='1' order by time LIMIT 10");

• LIMIT 10 applies for comments as whole.

Thanks in advance for all advices and answers. Ps. I'm sorry for my english. Peter

Answer 1

LIMIT 10

says that the result will contain 10 lines from the HOLE query output.

Lets say in database you have 2 posts: post1 contain 5 related comments, and post2 contain 10 related comments.

executing the query: SELECT * FROM comments WHERE pid IN ({$decodedPostIds}) AND state='1' order by time

will return:

• post1: comment1
• post1: comment2
• post1: comment3
• post1: comment4
• post1: comment5
• post2: comment1
• post2: comment2
• post2: comment3
• post2: comment4
• post2: comment5
• post2: comment6
• post2: comment7
• post2: comment8
• post2: comment9
• post2: comment10

Now, adding LIMIT 10 to query, will return the FIRST 10 lines of the hole result, meaning from post1: comment1 to post2: comment5

You have 2 solutions:

1. make a loop for each post and execute your query on that post:

   SELECT * FROM comments WHERE pid = $post_id AND state='1' order by time LIMIT 10

2. fetch ALL posts and, using PHP code, group the first 10'th comments of each post

pseudo code:

$rows = mysqli_query($con,'SELECT * FROM comments WHERE WHERE pid IN ({$decodedPostIds}) AND state='1' order by time LIMIT 10');

foreach($rows as $row){
    if(count($arr[$row['post_id']]) < 10){
        array_push($arr[$row['post_id']],$row)
    }
}

now $arr is array where each key is post_id with it 10'th comments as value.

IMO: I prefer solution 2(hate to execute queries in loop).

Hope it help a bit.