MySQL Selecting records by multiple IDs with LIMIT

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

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:

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