Posted by Tom Crimmins on 04/20/05 18:28

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

[Back to original message]