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:doug@stripped]
> Sent: Friday, June 02, 2006 10:42 AM
> To: Miles Thompson
> Cc: mysql@stripped
> 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
> Doug@stripped
>
>
>
> 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?
> >> unsub=miles@stripped
> >>
> >>
> >
> >
> > --
> > 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/mysql?unsub=1
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
