3

For mysql table, I have 10 rows and 5 (half) of them have post_id of 5 while the other 5 rows have post_id of 234.

I want to choose 2 rows from each post_id based on the date:

This is what I have so far

 $post_ids = $_POST['id'][0];
 $ids      = implode(',', $post_ids); //id's in ',' format

 $query    = "SELECT * FROM $table WHERE post_id in ({$ids}) ORDER by date desc limit 2";

This only gets first two results that matches one of two ids. Do I need to run foreach function for the each query? Is there another method?

Thanks!

Aniket Sahrawat
  • 12,410
  • 3
  • 41
  • 67
Steve Kim
  • 5,293
  • 16
  • 54
  • 99
  • 1
    yes put your query in foreach loop which fetch all the records of the db and then put the query inside the loop – Manthan Dave Nov 10 '16 at 05:22
  • Thanks for the help. Looks like this is the simplest method then. Thanks! – Steve Kim Nov 10 '16 at 05:24
  • didn't understand properly, but have you considered [GROUP BY](https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html) – bansi Nov 10 '16 at 05:24
  • Hi. I have two ids in which each ids have multiple rows. (for example, id (5) = row 1,row 2, row 3, row 4 etc). I am trying to select total of 4 rows (two from id = 5 and 2 from id = 234). Care to show me GROUP BY example? :) – Steve Kim Nov 10 '16 at 05:31
  • that is the exact thing the link in my previous comment do. please check that – bansi Nov 10 '16 at 05:32
  • I will take a look at the other post. Thanks! – Steve Kim Nov 10 '16 at 05:32

1 Answers1

3

You can use UNION ALL for this task. Try this:

$query = "( select * from $table where post_id = $ids[0] order by date desc LIMIT 1 ) UNION ALL ( select * from $table where post_id = $ids[1] order by age desc LIMIT 1 )";
Aniket Sahrawat
  • 12,410
  • 3
  • 41
  • 67