Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    10

    Unanswered: Updating DB on 5 minute intervals

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Posts
    10
    Thank you once again Ronan. This is exactly what I was looking for.

  4. #4
    Join Date
    Jan 2012
    Posts
    10
    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?

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Jan 2012
    Posts
    10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •