RE: Automatically add +1 every 30mins
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]
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]
Re: Automatically add +1 every 30mins
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/[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]
Re: Automatically add +1 every 30mins
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/[EMAIL PROTECTED]
Re: Automatically add +1 every 30mins
At 07:58 AM 6/2/2006, 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. I don't know about MySQL, but for PHP you would have to run it as a cron (or similar OS service) to trigger a script to do the update. If you have access to the server, this could be any language which can work with MySQL: Perl, Java, VB, C ? Or run JavaScript on the player's page? As for MySQL, I do not believe it has a built-in timer, except for synchronization or replication. Miles Thompson -- 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]