Updating DB on 5 minute intervals

rickymm3 · #1 (**permalink**) 02-06-12, 13:02

I need to update a column of data every 'x' minutes. I've browsed around looking for info on how to accomplish this, but had no luck.

I got some great help in my last thread, so hopefully I will have the same luck.

In my case, I am making a webgame. Players receive a "turn" every ~5 minutes.

I'd prefer everyone to get their turn at the same time. They will also have to receive it when they are not logged in, so its a global update.

Any suggestions on how to go about accomplishing this? I'm hoping sql/php has some built in function that makes this a simple task.

it-iss.com · #2 (**permalink**) 02-06-12, 16:47

Have a look at MySQL EVENTS, this is an in built scheduler in MySQL allowing you to run queries against the database at pre-determined times. These are available on MySQL 5.1 or later so check the version of MySQL that you are running.

Here is an example:

Code:

CREATE EVENT myevent
    AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTES
    EVERY 5 MINUTES
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

rickymm3 · #3 (**permalink**) 02-07-12, 12:29

Thank you once again Ronan. This is exactly what I was looking for.

rickymm3 · #4 (**permalink**) 02-08-12, 13:44

I attempted to test out this solution.

unfortunately, it appears my web host does not have this enabled...and I'm not entirely sure if I have the capability of doing it myself. When I enter the enabling code, it tells me :

Access denied; you need the SUPER privilege for this operation

I'm in phpmyadmin, and should have all privileges granted.

Is there anything I can do short of creating my own server?

If not, is there any other suggestions on how to do this?

it-iss.com · #5 (**permalink**) 02-09-12, 12:50

Super privileges is basically DBA privileges on a server. If this is a hosted database server then it is unlikely that you will be granted such permissions. The only alternative I can think of is to have a job scheduled in the background each 5 minutes performing your update statement. If you are using a Linux/UNIX type server then you can script this as follows:

Create a file called update.bash with the following contents on the server. Keep a note of the location of this file.

Code:

#!/bin/bash
/usr/bin/mysql -D database -u username -ppassword <<!
UPDATE statement ...;
!

Then in the scheduler you simply reference the full path location to this file and schedule to have this run every 5 minutes.

Hope this helps!!

rickymm3 · #6 (**permalink**) 02-09-12, 13:58

Thanks again. It is certainly something to look into. And I will be.

Having no access to the event scheduler, I decided on an alternative method for gaining turns: Requiring the user to manually "accept" them by clicking on a link/button.

What I did was record the last time they clicked the "Harvest" button, which would grant them "turns."

Then everytime they click it again, it will grant them more turns based on how many 5 minute intervals go by.

The tough part was figuring out how not to lose the "remaining" time in between each interval. For this, I chose to round down the Currenttime to the previous 5 minute interval and record it.

The Code I came up with is below: EXTREMELY UGLY! Be warned.
I removed the SQL so the top part might be incorrectly syntaxed.

Code:

<?php

$lastharvest = '2012-02-09 11:54:10'

$strlh = strtotime($lastharvest[0]);
$curdate = date ('Y-m-d H:i:s');
$strcurdate = strtotime ($curdate);
$timediffsec = $strcurdate-$strlh;
$mins = $timediffsec/60;
$turns = floor($mins/5);
echo "Last Harvest: $lastharvest[0]";
echo "<br>Current Date: $curdate";
echo "<br>Last Harvest (unix: $strlh";
echo "<br>Current Date (unix): $strcurdate";
echo "<br>Difference in time (unix): $timediffsec";
echo "<br>Minutes Passed: $mins";
echo "<br>Turns Gained Since Last Harvest: $turns";
}
/* Make '$newturns' Variable to be sent to DB to update tbl 'user_stats'.'turns'*/
$query = "SELECT turns FROM user_stats WHERE user_id='$user_id'";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
$curturns = mysql_fetch_row($result);
$newturns = $curturns[0]+$turns;
echo "<br><br>Current turns: $curturns[0]";
echo "<br>New Total Turns: $newturns";
if ($newturns > 200) {
$newturns = 200;
}
echo "<br>Total Turns to be Recorded: $newturns";

/* Make new 'curdate' variablo, rounded down to earliest 5 minute intervale */
$rndcurdate = date('i', $strcurdate);
$rndmin = $rndcurdate - ($rndcurdate % 5);

$arrayCurdate = getdate($strcurdate);
echo "<br>arrayUnix: $arrayCurdate[0]";
echo "<br>arrayMinutes: $arrayCurdate[minutes]";
$arrayCurdate[0] = $arrayCurdate[0] - ($arrayCurdate[minutes]*60) + ($rndmin*60);
echo "<br>arrayUnix after math: $arrayCurdate[0]";
$minCurdate = date('Y-m-d H:i:s', $arrayCurdate[0]);
echo "<br>";
echo "<br>Time Rounded Down to lastest Five Minute interval: $rndmin";
echo "<br>Above as array: $arrayCurdate[minutes]";
echo "<br>New Current Time to be Recorded: $minCurdate";
?>

Again, I ask for any advice/criticisms on the code. Better suggestions on how to do it etc.

I'm almost sure there is a better way to record the rounded down currenttime. But I did what I could. Seconds exist..but will be ignored...so I can probably trim them off.

Internet Services

Web Development

Digital Marketing

Consumer Tech