删除不在我的数据库中的文件

I'm trying to delete photos from a folder, which is called, "photos", that are NOT currently in my database. (These are all photos that have stacked up as I've been building the website and testing it and such, so now it's time to take the site live and I don't want all this waste in it)

I have three tables with photo information in them, and a number of columns throughout. Below is a mockup query of about what I THINK it should look like.

SELECT left_image, right_image, photo1, photo2, photo3, photo4, home_photo 
FROM about_photos, facilities, home

left_image and right_image go with about_photos. photo1, photo2, photo3 and photo4 go with facilities. home_photo goes with home.

Also, I need to use a wildcard for the end of the photo, because the files have thumbnails, so for instance the original photo would be called, abcimage.jpg but there would be abcimage.jpg, abcimage_medium.jpg, abcimage_thumb.jpg also in the database I only store, photos/abcimage and add the rest of the filename depending on where it goes.

$directory = "../path/to/photos_directory/";

//get all image files with a .jpg extension.
$images = glob($directory . "*.jpg");

foreach($images as $image)
{
    $name = explode('_',$image);
    $name = 'photos/' . $name[0];
    $sql = mysql_query("SELECT id FROM table WHERE photo1='$name' OR photo2='$name'");
    if(mysql_num_rows($sql) == 0)
        unlink($directory . $image);
}

You have two options:

One:

• With an SQL query, get a complete list of all the photos referenced in your database
• iterate through the files in your directory
  • if the file is an image AND it is not in your list, delete the file.

Two:

• iterate through the files in your directory
  • if the file is an image
    • query the database for that filename
      • if the response is empty, delete the file

The exact SQL query depends on your table structure, which you have not provided.

The best option depends mostly on scale. If there are lots of images in the database, then the first option involves having a very large list in memory. However the second version involves many more database queries. So it's a tradeoff.

There are more sophisticated options involving caching and pre-emptive queries, but I imagine you don't want to go that deep yet.

Something like the following. I've also got original files in the folder and I limit to 500 deletions at a time. Tweak as you need. Hope it saves somebody time...

<?php

require 'session.php';
require 'settings.php';

/* Execute the query. */  
$DBH = new PDO($mssql_driver.'='.$mssql_server.';'.$mssql_dbkey.'='.$mssql_database,        $mssql_username, $mssql_password); 
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  

$tsql = 'select * from ProductImage';
$PRE = $DBH->prepare($tsql);
$PRE->execute();

$pictures =$PRE->fetchAll(PDO::FETCH_ASSOC);

$directory = $_SERVER["DOCUMENT_ROOT"]."\..\..\products\pictures/";

//get all image files with a .jpg extension.
$images = glob($directory . "*.jpg");

$counter =0;

foreach($images as $image)
{
   $name = explode('pictures/',$image);
   $name =  $name[1];

    $foundImage = false;

    print  "checking: ".$name;

    foreach ($pictures as $picture){

         $original_file = explode('.', $picture['Image_Big']);
         $original_file = $original_file[0].'-original.'.$original_file[1];


         if ( ($picture['Image_Small'] == $name)|| ($picture['Image_Big'] == $name) || ( $original_file == $name) || ($picture['Image_Thumb'] == $name) || ($picture['Image_PriceList'] == $name)){
               $foundImage = true;  
               break;       
         }
    }

    if (!$foundImage) {
         unlink($directory . $name);

         print "....deleting";

         $counter += 1;

     }

     print "<br />";



     if ($counter> 500){ 
         exit;
     }
}

?>