Posted by John Nichel on 04/19/05 17:08

Petar Nedyalkov wrote:
<snip>
> You can store only 10 records for each user by using the following logic:
>
> mysql> show create table profile_log\G
> *************************** 1. row ***************************
> Table: profile_log
> Create Table: CREATE TABLE `profile_log` (
> `profile_id` int(10) default NULL,
> `user_id` int(10) default NULL,
> `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
> 1 row in set (0.00 sec)
>
> When you create a profile you fill 10 blank records with timestamp 0000-00-00
> 00:00:00.
>
> Then if a user sees a profile:
>
> PSEUDO SQL:
>
> $SQL = "UPDATE profile_log SET user_id=".$userId." WHERE profile_id=".
> $profileId." ORDER BY timestamp ASC LIMIT 1";
>
> This way you:
> 1. automatically get the new timestamp
> 2. have no more than 10 records per user.

What version of MySQL does that work in? I tried pasting that create
table directly into MySQL and get an error...

#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP
) ENGINE=MyISA

Query used :

CREATE TABLE `profile_log` (
`profile_id` int( 10 ) default NULL ,
`user_id` int( 10 ) default NULL ,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
) ENGINE = MYISAM DEFAULT CHARSET = utf8

I can't find a reference to this on MySQL's site...would you have a link
to the documentation? Thanks.

--
John C. Nichel
ÜberGeek
KegWorks.com
716.856.9675
john@kegworks.com

[Back to original message]