Posted by Christopher Fulton on 04/19/05 20:30

> I did, the problem is the client is on a box with mySql 3.23.x or 4.0.x, he
> is deciding to upgrade to a dedicated box but then the host he is looking at
> says they will charge him if they are to upgrade to mysql 4.1 (hence i cant
> even use sub-selects)
> (Sorry I didnt mention the MySql version before...didnt think it was
> important till now)

The MySQL version should not matter for what he was saying (you don't
need subqueries). I think he was saying that every time you insert a
user, also insert 10 records into the users "profile_viewed_log." with
an empty timestamp. Then, every time someone views the profile,
update the profile_viewed_log, instead of inserting into it. (which
can be done with a simple query...)
$SQL = "UPDATE profile_log SET user_id=".$userId.",
date_entered='1113931530' WHERE profile_id=".
$profileId." ORDER BY date_entered ASC LIMIT 1";

(the date i have is a unix timestamp, but you can use whatever format
you wish).

I havn't tested that query, but it should work with no problem.
Basically, it just updates the first record with the "smallest"
timestamp.

Then, when you want to show the user the views for his/her profile,
you just exclude the one's with an empty timestamp.

Your other option, which also would work well, would be to do the cron
job, with a limit value on the query. IMHO this is better than 3
queries every time someone views a profile. Also, you can set the
cron job to run at 3AM, when there are not likely to be many users.

Hope this helps some.

-Chris

[Back to original message]