I am trying to execute a query that deletes all files from the images table that do not exist in the filters tables. I am skipping 3,500 of the latest files in the database as to sort of "Trim" the table back to 3,500 + "X" amount of records in the filters table.

The filters table holds markers for the file, as well as the file id used in the images table.

The code will run on a cron job.

My Code:

$sql = mysql_query("SELECT * FROM `images` ORDER BY `id` DESC") or die(mysql_error());

while($row = mysql_fetch_array($sql)){
    $id = $row['id'];
    $file = $row['url'];

    $getId = mysql_query("SELECT `id` FROM `filter` WHERE `img_id` = '".$id."'") or die(mysql_error());
    if(mysql_num_rows($getId) == 0){        
        $IdQue[] = $id;
        $FileQue[] = $file;
    }
}
for($i=3500; $i<$x; $i++){
    mysql_query("DELETE FROM `images` WHERE id='".$IdQue[$i]."' LIMIT 1") or die("line 18".mysql_error());
    unlink($FileQue[$i]) or die("file Not deleted");
}
echo ($i-3500)." files deleted.";

Output: 0 files deleted.

Database contents:

images table: 10,000 rows
filters table: 63 rows

Amount of rows in filters table that contain an images table id: 63 Execution time of php script: 4 seconds +/- 0.5 second

Relevant DB structure

• TABLE: images
• id
• url

• etc...

• TABLE: filter

• id
• img_id (CONTAINS ID FROM images table)
• etc...