CODEWITHSUNDEEP
phpmysqlwhile-loopcron
user1342220
user1342220

Reputation: 73

Best way to handle a large while loop in PHP

I have a script that runs via CRON that processes each row (or user) in one of the tables in my databases, then uses cURL to pull a URL based on the username found in the row, and then adds or updates additional information into the same row. This works fine for the most part, but seems to take about 20 minutes+ to go through the whole database and it seems to go slower and slower the farther it is into the while loop. I have about 4000 rows at the moment and there will be even more in the future.

Right now a simplified version of my code is like this:

$i=0;
while ($i < $rows) {
    $username = mysql_result($query,$i,"username");
    curl_setopt($ch, CURLOPT_URL, 'http://www.test.com/'.$username.'.php');
    $page = curl_exec($ch);
    preg_match_all('htmlcode',$page,$test)
    foreach ($test as $test3) {
         $test2 = $test[$test3][0];
    }
mysql_query("UPDATE user SET info = '$test2' WHERE username = '$username');
    ++$i;
}

I know MySQL querys shouldn't be in a while loop, and it's the last query for me to remove from it, but what is the best way to handle a while loop that needs to run over and over for a very long time?

I was thinking the best option would be to have the script run through the rows ten at a time then stop. For instance, since I have the script in CRON, I would like to have it run every 5 minutes and it would run through 10 rows, stop, and then somehow know to pick up the next 10 rows when the CRON job starts again. I have no idea how to accomplish this however.

Any help would be appreciated!

Upvotes: 2

Views: 1218

Answers (3)

Patrick
Patrick

Reputation: 922

LIMIT and OFFSET are your friends here. Keep track of where you are through a DB field as suggested by Bastian or you could even store the last offset you used somewhere (could be a flat file) and then increase that every time you run the script. When you don't get any more data back, reset it to 0.

Upvotes: 0

Bastian Doeen
Bastian Doeen

Reputation: 128

About loading the data step by step:
You could add a column "last_updated" to your table and update it every time you load the page. Then you compare the column with the current timestamp before you load the website again.

Example:

mysql_query("UPDATE user SET info = '$test2', last_updated = ".time()." WHERE username = '$username');

And when you load your data, make it "WHERE last_updated > (time()-$time_since_last_update)"

Upvotes: 1

Christian Graf
Christian Graf

Reputation: 406

What about dropping the 'foreach' loop? Just use the last element of the $test array.

Upvotes: 0

Related Questions