Query broke down and left me stranded in the woods
- by user1290323
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...
