Kudos! I read a lot of replies to various threads hoping to learn something, and this thread was a double bonus.
Never new of the 'events' feature either. But I think this solution below is very elegant and is one of those "so obvious it makes me feel retarded" answers. ;-) Personally, I would have erroneously done the crontab way and never given it a second thought. This method opens my eyes to a new "outside the box" way of thinking. Even more ironic is that I used to code 3D video games for 3.5 years and this is EXACTLY how I would have had to implement something like this in a game. Even moving an object from a to b requires delta times. I took it for granted in that scenario. Using servers and databases, I take for granted crontabs and external scripts. I just found it very enlightening that this example merges those two worlds. Thank you Douglas (and Dan). daevid.com > -----Original Message----- > From: Douglas Sims [mailto:[EMAIL PROTECTED] > Sent: Friday, June 02, 2006 10:42 AM > To: Miles Thompson > Cc: mysql@lists.mysql.com > Subject: Re: Automatically add +1 every 30mins > > > You probably don't want to do it with events or as a cron job. As > it sounds from your description the amount of gold is a function of > time, perhaps time since they started playing or joined the game. > Thus, instead of updating every 30 minutes, when you are selecting > the current amount of gold, just select it as a function of the time > since they were online or signed up for the game. > > You can use a time difference function, either "TIMEDIFF" or > subtract > the UNIX_TIMESTAMP value of the start date/time from the > UNIX_TIMESTAMP() function of the curent date/time, and then just > divide appropriately. > > More info about MySQL date/time functions: http://dev.mysql.com/doc/ > refman/5.0/en/date-and-time-functions.html > > Example: To get the number of 30-minute increments since some date, > > mysql> SELECT ROUND((UNIX_TIMESTAMP()-UNIX_TIMESTAMP('2006-05-30 > 3:45'))/(60*30)); > +------------------------------------------------------------- > --------+ > | ROUND((UNIX_TIMESTAMP()-UNIX_TIMESTAMP('2006-05-30 > 3:45'))/(60*30)) | > +------------------------------------------------------------- > --------+ > | 162 > | > +------------------------------------------------------------- > --------+ > 1 row in set (0.00 sec) > > Here is a reference to the MySQL documentation on date and time > functions, which is really good: http://dev.mysql.com/doc/refman/5.0/ > en/date-and-time-functions.html > > Good luck! > > > Douglas Sims > [EMAIL PROTECTED] > > > > On Jun 2, 2006, at 7:56 AM, Miles Thompson wrote: > > > > > Dan, > > > > Did not know about events in MySQL. That's a terrific feature. > > > > Miles > > > > At 09:44 AM 6/2/2006, Dan Buettner wrote: > > > >> Alex, as Miles noted, this could easily be accomplished with an > >> external cron event. Doesn't have to be written in an external > >> language like PHP or perl, even - could be a self-contained > >> crontab entry a la: > >> > >> 0,30 * * * * /path/to/mysql -u user -psecret database_name -e > >> "update table_name set gold = gold + 1" > /dev/null > >> > >> > >> To accomplish this within MySQL, one option might be 5.1's events: > >> > >> http://dev.mysql.com/doc/refman/5.1/en/events.html > >> http://dev.mysql.com/doc/refman/5.1/en/create-event.html > >> > >> > >> Hope this helps, > >> Dan > >> > >> > >> > >> Alex Major wrote: > >>> Hi there. I've posted this up on both this list, and the > php list > >>> as I'm not sure > >>> whether this is something that I'd need to do with the > php or mysql. > >>> Basically, I am making an add-on to my small website which is a > >>> mini online > >>> game. Every user will have gold, and every 30mins I'd like their > >>> amount of > >>> gold to go up by 1 (or say a variable say $goldupdateamount). > >>> I'd like to know which would be the best way of doing this, and > >>> if there is > >>> a command in mysql which would achieve this. > >>> Regards, Alex. > >>> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/mysql? > >> [EMAIL PROTECTED] > >> > >> > > > > > > -- > > No virus found in this outgoing message. > > Checked by AVG Anti-Virus. > > Version: 7.1.394 / Virus Database: 268.8.1/354 - Release Date: > > 6/1/2006 > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
