Auto reloading script executing 20 rows at a time.

[andrewgarn]

Right, so i've established on my server I cant change the max execution time to above 30.

 

So for my massive update pages I need to update a few rows at a time, then reload the page and update the next few until I run out of rows.

 

However i'm not sure how to do this.

 

I understand adding to the end of select scripts: LIMIT 0, 19.

 

But how would i get the page to reload itself then execute rows 20, 39?

 

to get a understanding of what my codes doing:

 

<?PHP 
$sql = mysql_query("select from user LIMIT 0, 19")
while($r = mysql_fetch_array($sql)) {
      //retrieve information from a URL
      $sql2 = mysql_query("select from table2 where username = '$username'")
      while($r = mysql_fetch_array($sql2)) {
      //execute update query using info from url and table2
      }
}
?>

 

How would i edit that to echo some result, wait 5 seconds then, reload itself with limit 20, 39? - then 40, 59 etc

 

How would it know when to stop when it runs out of user rows?

 

I'm thinking a meta refresh, but how?

 

 

[teynon]

You can use variables via the url to keep track of the last page position:

 

for example:

 

file.php?start=0

<?php
$start=$_GET['start'];
$linesPer=20;

$sql="DO SOMETHING LIMIT {$start},{$linesPer}";

$newStart=$start+$linesPer;
// When finished:
header("Location: file.php?start={$newStart}");
?>

[andrewgarn]

That looks good, but how would it know when it runs out of rows?

 

will the header work? Previously when ive tried that it gives me an error saying header already set.

 

Number of users wont be fixed, as new users register

[teynon]

You can do two things for the header. You can put a buffer on it or just use javascript: <script>document.location.href='newfile.php?page=blah';</script>

 

As for running out of rows:

 

Depending on what your script is doing in MYSQL, you can count how many rows exist:

$mysql_rows=mysql_query("SELECT * FROM table");

$totalRows=mysql_num_rows($mysql_rows);

if ($start>$totalRows) { // Finished Executing. Dont go to new page. }

[andrewgarn]

You can do two things for the header. You can put a buffer on it or just use javascript: <script>document.location.href='newfile.php?page=blah';</script>

 

As for running out of rows:

 

Depending on what your script is doing in MYSQL, you can count how many rows exist:

$mysql_rows=mysql_query("SELECT * FROM table");

$totalRows=mysql_num_rows($mysql_rows);

if ($start>$totalRows) { // Finished Executing. Dont go to new page. }

 

That wont work if the last page got up to row 40, and the table ends on row 43? Or will it? If i set limit 40, 59 and there are only 43 rows what will mysql do?

 

the first mysql query just retrives the username from user table, then uses it in the next query

[TransmogriBenno]

$mysql_rows=mysql_query("SELECT * FROM table");
$totalRows=mysql_num_rows($mysql_rows);

This is a really inefficient way to count the number of rows, at least try

SELECT COUNT(*) AS NumRows FROM table_name

 

In response to the OP, I would probably add a DATETIME column called LastUpdated, which you update when you process the rows. You can then set up a cron job to run your script every minute -- or continue down the meta refresh mode, if you want to rely on a browser to do the updates -- and when the script runs it gets the 20 (or however many) rows that have the oldest (or NULL) LastUpdated value.

[lemmin]

I showed how you could do this in your other post:

"UPDATE [...] LIMIT 1 OFFSET ".$_GET['currentrow']";
header("Location: ?currentrow=".$_GET['currentrow']++);

 

Just change it to 20 if you only want to do 20 at a time and add an if statement to check if it was successful (if it wasn't you can stop):

if (mysql_query("UPDATE [...] LIMIT 20 OFFSET ".$_GET['currentrow']"))
   header("Location: ?currentrow=".$_GET['currentrow']+20);

 

You probably want this to start with, too:

if (!isset($_GET['currentrow']))
   $_GET['currentrow'] = 0;

 

I'm actually not sure if the first row's offset is 0 or 1, but I would assume it is 0.

[andrewgarn]

$mysql_rows=mysql_query("SELECT * FROM table");
$totalRows=mysql_num_rows($mysql_rows);

This is a really inefficient way to count the number of rows, at least try

SELECT COUNT(*) AS NumRows FROM table_name

 

In response to the OP, I would probably add a DATETIME column called LastUpdated, which you update when you process the rows. You can then set up a cron job to run your script every minute -- or continue down the meta refresh mode, if you want to rely on a browser to do the updates -- and when the script runs it gets the 20 (or however many) rows that have the oldest (or NULL) LastUpdated value.

 

Thats an interesting idea, however I dont have access to run a cron job every minute. At the moment I am using a free cron service to run the files once a day, then running manually as required.

 

Would a cron job not work with the meta refresh method?

 

As for the lastupdate, I actually am already using a lastupdate on user to check that the page that gets run once a day doesnt get run more than once a day