Posted by Tom Crimmins on 04/19/05 17:42

On Tuesday, April 19, 2005 09:09, John Nichel wrote:

> 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.

Try:

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

You do not need to specify the default clause on the timestamp column since
this is already the default behavior, and as you has seen this isn't valid
before 4.1 anyway. Also I wouldn't use the name 'timestamp' for a column
name.
It is generally not a good idea to use reserved words for column names.

--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

[Back to original message]