[SOLVED] Cron Job, query, loop, exec, results. HELP!!

[stopblackholes]

I set up a cron job and it works for the first result but crashes because second result is not a valid mysql resource in the while loop.  Also i foresee huge problems in the future with this. Each result takes about 5 mins to process. so if i set up cron to run every 2 minutes it will eventually run over the previous cron job?

 

I dont want hundreds of processes running and fetching the same results over and over.

 

this is the basic idea of it.

Cron.php

<?php

//select query get waiting
$query = "SELECT * FROM uploads WHERE upload_status='waiting' LIMIT 0,10";
$result = mysql_query($query) or die(mysql_error());

if (mysql_num_rows($result)> 0) {

while($rows = mysql_fetch_array($result)){
   $upload_id = $rows['upload_id'];
    $uploaded_file = $rows['upload_file'];
    $finished_file = "files/file.avi";

exec("ffmpeg -i ".$uploaded_file." -acodec mp3 -ab 48k -ac 1 -ar 44100 480x360 ".$finished_file);

unlink($uploaded_file);

$query = "UPDATE uploads SET upload_file='$finished_file', upload_status='finished' WHERE upload_id='$upload_id'";
$result = mysql_query($query) or die(mysql_error());

}

}
?>

[rhodesa]

You overwrite the value of $result. Use a different variable. Also, if you want to prevent collisions, you can try only selecting one at a time. After make the status pending. Then put that entire block of code inside a loop, that ends when there are no more waiting.

 

<?php
  while(1){
    //select query get waiting
    $query = "SELECT * FROM uploads WHERE upload_status='waiting' LIMIT 1";
    $result = mysql_query($query) or die(mysql_error());
    if (!mysql_num_rows($result)) break; //No more waiting
    $row = mysql_fetch_array($result);
    //Set to Pending
    mysql_query("UPDATE uploads SET upload_status='pending' WHERE upload_id='{$row['upload_id']}'") or die(mysql_error());
    $finished_file = "files/{$row['upload_id']}.avi"; //This is a unique name
    exec("ffmpeg -i ".$row['upload_file']." -acodec mp3 -ab 48k -ac 1 -ar 44100 480x360 ".$finished_file);
    unlink($row['upload_file']);
    mysql_query("UPDATE uploads SET upload_file='$finished_file', upload_status='finished' WHERE upload_id='{$row['upload_id']}'") or die(mysql_error());
  }
?>

[stopblackholes]

ok thanks i will try that out.  looks like it work.

 

i was thinking about setting up some type of file lock to prevent extra processes and overhead from ffmpeg but maybe if i just set to pending like you did  2 or more processes wont be a problem.

[rhodesa]

That is a good point, you can flock the 'upload_file'. Just open it in read mode with fopen, the establish an exclusive lock. Just make sure you use non-blocking or whatever it is, so that any other process that tries to open it doesn't wait, but rather just returns false so it can continue to another file.

[stopblackholes]

ok added a exclusive lock works good now. thanks for the help