On Tuesday 19 April 2005 17:42, Tom Crimmins wrote: > 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. I just demonstrated a concept. The guy has to do this the best way he can ;-) It's not a "do-my-work-while-i'm-taking-a-rest" mailing-list ;-) > It is generally not a good idea to use reserved words for column names. > > -- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436
Attachment:
pgpFkDLOVdCb6.pgp
Description: PGP signature
