SQL Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

SQL Help

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases.[/url]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

add id to the selection list?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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;
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why not sort by the date?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Since the id field is incremented with each entry, the ORDER BY id DESC would find the latest date entry.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
heavyj
Forum Newbie
Posts: 2
Joined: Tue Oct 11, 2005 9:11 pm

Post by heavyj »

Could you post the schema of your blogs table as well as a more detailed description of the task?
Post Reply