结果回到每一行

Question

$friendship = mysql_query("SELECT * FROM friends WHERE accountid='$row[id]' && status='approved'") or trigger_error(mysql_error());  
while($joysong2 = mysql_fetch_array($friendship))
{
    $newnot=mysql_query("SELECT * FROM notifyme where userid='$joysong2[friendid]' ORDER BY date DESC, time DESC LIMIT 0,5") or trigger_error(mysql_error());
    while($notify = mysql_fetch_array($newnot))
    {

 }
}

Hi guys let me explain what I am trying to do here.

My $friendship query is giving us back a result of the users who the user has approved to communicate with.

So the result would come back as 1,3,4,5 depending on the other users user id.

The second query $newnot is using the information $friendship and bringing back 5 entries in the table notifyme.

Problem is right now that I'm getting 5 results for each user.

What I wanted was only 5 results in total from notify me in date/time order.

eg.

1 posted 1/08/2015
1 posted 2/08/2015
3 posted 1/08/2015
4 posted 2/08/2015
3 posted 3/08/2015

I would want them back as

3 posted 3/08/2015
4 posted 2/08/2015
1 posted 2/08/2015
3 posted 1/08/2015
1 posted 1/08/2015

right now though my results are coming back as

1 posted 1/08/2015
1 posted 2/08/2015
1 posted 1/07/2015
1 posted 1/06/2015
1 posted 1/05/2015

3 posted 3/08/2015
3 posted 1/08/2015
3 posted 1/07/2015
3 posted 1/06/2015
3 posted 1/05/2015

I guess you get what I mean. So how do I get this to work?

Answer 1

I suppose this will help you better, but consider some error because I didn't test it. You are welcome to show errors until we fix it. replace some column names with the one you use:

SELECT accountid, status, cnt_rows, `date` FROM friends f

LEFT JOIN (
  SELECT COUNT(*) AS cnt_rows, `date`, f_id FROM notifyme n) AS n_cnt
  ON f.id=n_cnt.f_id
)
GROUP BY n_cnt.date
WHERE status='approved'
OREDER BY `date` DESC
LIMIT 5