I thought I was doing the right thing but I keep getting the wrong result. I am trying to simply find the id of the entry with the min time, but I am not getting that entry.
$qryuserscount1="SELECT id,min(entry_time) FROM scrusersonline WHERE topic_id='$topic_id'"; $userscount1=mysql_query($qryuserscount1); while ($row2 = mysql_fetch_assoc($userscount1)) { echo $onlineuser= $row2['id']; }
That is my query, and it does not work. This however does work which does not make sense to me SELECT id FROM scrusersonline WHERE topic_id=’$topic_id’ ORDER by entry_time LIMIT 1, can anyone quickly point out what I am doing wrong?
Advertisement
Answer
SELECT id,min(entry_time) FROM scrusersonline WHERE topic_id='$topic_id'
will return id
and entry_time
for every row matching topic_id
, since there is no group-by clause from which the min(entry_time)
could be selected. The SELECT
clause only defines what columns are to be returned, it doesn’t restrict that selection by rows any – that’s what the WHERE
clause is for.
SELECT id FROM scrusersonline WHERE topic_id='$topic_id' ORDER BY entry_time LIMIT 1
will return id
(SELECT id
) for only the first (LIMIT 1
) matching topic_id
(WHERE topic_id='$topic_id'
) when ordered by entry_time
(which is the minimum).
So, yes, SELECT id FROM scrusersonline WHERE topic_id='$topic_id' ORDER BY entry_time LIMIT 1
is what you’re looking for.