$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?
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