RE: [PHP] Last visitors

On Wednesday, April 20, 2005 09:57, Ryan A wrote: > <clip 1> >>> Thanks for replying, I tried using the test example of Petar >>> Nedyalkov, but when i try to create the following: >> >>> CREATE TABLE profile_log ( >>> profile_id int(10) default NULL, >>> user_id int(10) default NULL, >>> last_login timestamp NOT NULL >>> ) ENGINE=MyISAM CHARSET=utf8 >> >>> I get an error on both >>> ENGINE=MyISAM >>> and >>> CHARSET=utf8 >> >>> What are they for really? can I omit them? or will that effect the > program later on? > </clip 1> > > >> You can leave these off. Changing ENGINE to TYPE will fix that >> error, and you can leave off the charset. I believe utf8 was added >> in 4.1. If you leave off charset=xxx it will just default to >> whatever the server's default-character-set is set to, probably >> latin1. > > > Hey, > Thanks for replying. > > Theres something wrong coz its not working as expected... > I followed instructions and created a table like this: > > > CREATE TABLE test_last_visitors ( > profile_id int(10) default NULL, > user_id int(10) default NULL, > ttimestamp timestamp(14) NOT NULL) TYPE=MyISAM; > > then I ran this 5 times: (logic being: test with 5, if it works go > with 10 or more) :-) > insert into test_last_visitors values(1,1,now()); > > then from my php script (test_last_visitors.php) I ran this test SQL: > $SQL = "UPDATE test_last_visitors SET profile_id=".$profile_id.", > user_id=user_id+1, > ttimestamp=now() WHERE profile_id=1 ORDER BY ttimestamp ASC LIMIT 1"; > > > The first 5 times it works perfectly, then after that it updates only > the latest record over and over again :-( > I tried playing with the ASC and DESC, MIN and MAX....no joy. > > Any idea whats wrong? > > Thanks, > Ryan The following works for me. UPDATE test_last_visitors SET user_id=user_id+1 WHERE profile_id=1 ORDER BY ttimestamp LIMIT 1; You don't have to explicitly update the timestamp column. It will do this automatically. This will rotate through the five rows. This may have failed in your test because if you executed this in a script and all of the sql statements occurred in the same second, you would probably see the behavior that you described. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa