Posted by Matthew Weier O'Phinney on 04/16/05 17:29

* Ryan A <ryan@coinpass.com>:
> My host has MySql 4.0.x so I cant really use the nested selects of 4.1 :-(
> please tell me how you solved something like this;
>
> I have two tables:
> users_online (username , dateandtime)
> users_details (cno, username, has_image, image_name)
>
> 1a) I have to select x records from users_online where $username is not more
> than 3 minutes old,
> 1b) then I have to check if that username has_image,if yes, get the
> imagename
>
> I thought of having the imagename also in the users_details, but we are
> allowing our members to
> change/modify their images at will....
>
> What kind of sql do I use above? I dont want to run a query on every record
> returned from users_online
> as that would be really "expensive".

You're right, this is OT, but I'll answer anyways.

You need to start brushing up on ANSI SQL, as this requires only a JOIN
and a LIMIT:

SELECT
users_details.username,
image_name
FROM
users_details
INNER JOIN
users_online ON users_online.username = users_details.username
WHERE
users_online.dateandtime < ? AND
has_image = 'yes'
LIMIT x

(where '?' is a date or timestamp 3 minutes in the past, and 'x' is the
number of records you wish to select).

--
Matthew Weier O'Phinney | WEBSITES:
Webmaster and IT Specialist | http://www.garden.org
National Gardening Association | http://www.kidsgardening.com
802-863-5251 x156 | http://nationalgardenmonth.org
mailto:matthew@garden.org | http://vermontbotanical.org

[Back to original message]