Page 1 of 1

SQL Help

Posted: Sun Oct 16, 2005 5:54 am
by s.dot
I have a comma delimited list of usernames, that I use in a query, to avoid doing a query inside of a while loop and having 50 extra queries.

It looks like this:

Code: Select all

$btresult = mysql_query("SELECT username, date, time FROM blog WHERE username IN ('$friends') GROUP BY username ORDER BY id DESC");
while($btarray = mysql_fetch_assoc($btresult))
{
	$thetime = $btarray['date'].' at '.$btarray['time'];
	$btime[$btarray['username']] = $thetime;
}
print_r($btime);
My problem is that no matter how I ORDER BY in the query, the date and time selected is always from the first row, not the last (as indicated by the order by id DESC).

I believe that the problem is in the GROUP BY part. I tried taking that out, and it didn't return anything.

Any help?

Posted: Sun Oct 16, 2005 10:05 am
by John Cartwright
Moved to Databases.[/url]

Posted: Sun Oct 16, 2005 10:29 am
by feyd
add id to the selection list?

Posted: Sun Oct 16, 2005 7:41 pm
by s.dot
Nope, its still showing the same thing.

For example it should be showing the last date as October 9th, 2005 at 3:19 PM (the latest entry), instead it says January 26th, 2005 at 3:44 AM (the first entry).

Code: Select all

$btresult = mysql_query("SELECT id, username, date, time FROM blog WHERE username IN ('$friends') GROUP BY username ORDER BY id DESC");
while($btarray = mysql_fetch_assoc($btresult))
{
	$thetime = $btarray['date'].' at '.$btarray['time'];
	$btime[$btarray['username']] = $thetime;
}

Posted: Sun Oct 16, 2005 8:08 pm
by feyd
why not sort by the date?

Posted: Sun Oct 16, 2005 8:20 pm
by s.dot
Since the id field is incremented with each entry, the ORDER BY id DESC would find the latest date entry.

Posted: Sun Oct 16, 2005 10:19 pm
by heavyj
Could you post the schema of your blogs table as well as a more detailed description of the task?