+1 Every Second

[Ndogg]

How do I make it so every second a certain row in the DB goes up +1, and this is happening constantly, the number never stops going up unless it reaches its max.

[ckeyrouz]

what version of MySQL you are using.
Am asking this because since the version 5.0 you can find in MySQL a schedular with which you can schedule any task you want.

You create a stored procedure that updates the field in the table as you have asked:

update myTable set myField = muField + 1;


and then using the schedular you can set it to execute this stored procedure every one second.

This is the solution, but from the other side, do you think it is healthy to update a record in the database every 1 second. If another resource is using this table then there will be waiting, lots of waiting.

What is the purpose of your demand, maybe we can find a better alternative.

[Ndogg]

Thanks all try that,
But what I need this for is for making a game. So you get for example +1 gold every second, if it gets bad, I could edit it to +60 every minute or something?

[ckeyrouz]

You can set the incrementation as a global variable and increment it every second if you need the precision and you can update the game's database every minute.

[Ndogg]

Where do I find the schedulur?
Also, for soem reason, its not updating the row +1, its just set the row to 1.


$mq = mysql_query("SELECT * FROM `users` WHERE `turns`='test'") or die(mysql_error());
$row = mysql_fetch_assoc($mq);
$turns = $row['turns']+1;
mysql_query("update users set turns = '$turns'");


"test" is the test user in which im testing this to changes turns +1


Edit: I took the ='test' off and it works

[ckeyrouz]

you should use this type of query:

update users set turns = turns + 1 where user='something here'

[Ndogg]

Ok, that works, but im stuck on the schedule thing, how do I do that?

[ckeyrouz]

You want the scheduling on the database level?

And can you specify please the version of MySQL you are using?

[Ndogg]

Yes


5.0.51

[ckeyrouz]

Sorry for the mistake I have committed, to enable events in MySQL you should have the version 5.1.* and above. (the * I think goes for 16).

So it won't work on 5.0.*

anyway here is the syntax:
CREATE EVENT myEvent
ON SCHEDULE EVERY 1 SECOND starts now()
DO update stdb.test set zCounter = (zCounter + 1);

[Ndogg]

I am getting the same version 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

[ckeyrouz]

Check this url it contains all the syntax of evens in mysql:
http://dev.mysql.com/doc/refman/5.1/en/create-event.html