CODEWITHSUNDEEP
phparraysselectmysqli
Petr Bielak
Petr Bielak

Reputation: 653

MySQL Selecting records by multiple IDs with LIMIT per each ID

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");

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

Upvotes: 2

Views: 2138

Answers (1)

YyYo
YyYo

Reputation: 641

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.

Upvotes: 1

Related Questions