Query broke down and left me stranded in the woods

Posted by user1290323 on Stack Overflow See other posts from Stack Overflow or by user1290323
Published on 2012-10-16T22:28:47Z Indexed on 2012/10/16 23:00 UTC
Read the original article Hit count: 227

Filed under:
|
|

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...

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql