best method for updating mysql

Hey all, I am trying update mysql database I am able to update every person if I use the browser it will cycle through everybody but I am using PHP Headers and cron jobs do not like those nasty buggers.

My question is do you guys know a good alternative to updating about 6k player stats ? I am limited to about 25 per loop anything more it will error 500 on me

I want to be able to update everybody ever 48 hrs or longer

I will also be adding some logic that says if they are already updated within the last XX hours skip

Thanks!

 

Well you can use a Javascript redirect and sleep() at the end of each page, and so make an script that keeps running. That's how I did the forum scraper. But I'm not sure that's the best approach for what you are doing.

 

Roughly

PHP:

<?php
    define("LocalCharCacheTable",<table name>);
    define("LastUpdated",<DB field name>);
    define("CharacterID",<DB field name>);
    define("CharsPerLoop",25);
    define("MinUpdateLimit",<in seconds>);
 
    $update=1;
 
    while($update) {
        $loop=array();
     
        $sql="select ".CharacterID."
                    from ".LocalCharCacheTable."
                    where (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(".LastUpdated."))>".MinUpdateLimit."
                    order by ".LastUpdated."
                    limit ".CharsPerLoop;
                 
        $q=mysql_query($sql);
     
        if($q) {
            while($row=mysql_fetch_assoc($q)) $loop[]=$row;
        } else {
            <error handling>  
        }
     
        $update=count($loop);
     
        foreach($loop as $char) {
            <fetch API data of $char>
            <process and update>  
        }      
    }
?>

 

feiXue,

The vast majority of web hosts, VPS, and even dedicated servers are set to throw a timeout after 30 seconds of operation by a PHP script. I don't know any configuration that will allow indefinite operation.

 

bah! I should of mentioned that I can currently update people on the fly when they visited >30 mins DO UPDATE but I want to keep EVERYBODY updated here is something I can have right now

Code:

$begin = $_GET['start'];
$finish = $begin + 10;
if ($begin == "") {
    $getdb_stats = $dbh->query("SELECT character_number FROM characters LIMIT 0 , 10");
    $getdb_stats->setFetchMode(PDO::FETCH_ASSOC);
}
elseif (isset($begin)) {
    $getdb_stats = $dbh->query("SELECT character_number FROM characters LIMIT " . $begin . " , 10");
    $getdb_stats->setFetchMode(PDO::FETCH_ASSOC);
}
while ($get_data = $getdb_stats->fetch()) {
    $char_id = $get_data["character_number"];
 
    $ApiLink    = "https://census.daybreakgames.com/get/ps2-beta/character/" . $char_id . "?c:show=stats,type,id,name,experience,times,profile.active_name.en&c:resolve=world&c:resolve=outfit";
    $json_decode = (json_decode(@file_get_contents($ApiLink), true));
    $output      = $json_decode["character_list"];
 
//UPDATE CHAR FUNCTIONS
}
$statement = $dbh->prepare("SELECT * FROM characters");
$statement->execute();
$total_chars = $statement->rowCount();
 
if ($_GET["start"] >= $total_chars) {
    ?>
    <meta http-equiv="refresh" content="0;URL='update_complete.php?update=true'"/>
<span style="color: #0000BB"><?php

The above works BUT! I have to keep my browser open and it will go through the entire DB and update x10 people at a time MAX 30 I am wondering if there is a way to have it go through the entire DB and update I know that Cronjob do not like http headers so it works until the refresh and it will crash ;(

Any ideas ?

 

I'm obviously missing something here. Script execution time restrictions are for one process. Set up constants in my script so that single call does not exceed those restrictions. Same script can be called multiple times; yes, some overlapping will occur but if you set up CharsPerLoop small enough it'll be fine.

Minimal interval you can set up with cron is one minute. If that's not often enough then create multiple scripts and modify SELECT statement for each to fetch only say PRIMARY_KEY%3==0, PRIMARY_KEY%3==1, etc ... and set crons for each at the same time. Cron jobs are not blocking, each subsequent will not wait for previous to finish.

Finally, if you couple whole thing with subrutine that checks API:last_saved for each character then you can skip those that weren't changed since you last pulled data.

 

You have to build a cronjob.

- Fetch a list of character_id that needs to be updated and make an array() of them
ex: array(1111111111,2222222222,3333333333,4444444444,.....)

- Make a function($characterIdList) that take your array as an argument
- In that function, you will divide $characterIdList into multiple pool of Ids for the sake of SOE's server load; and store them in a single multidimensional array
ex: array ( array(1111111111,2222222222,3333333333); array(4444444444,5555555555,6666666666), etc ... );

- for each $pools you will build and process a query to the API:

query: http://census...?id=1111111111,2222222222,3333333333
storing response...

query: http://census...?id=4444444444,555555555,6666666666
storing response...

etc...

with this methods it taks me verry little time to fetch 1000 players or so (less than 10 min)

If your problem reside more in your side (for exemple a CLI time limit), try including this code into your cronjob script:

http://php.net/manual/en/function.set-time-limit.php