[SOLVED] deleting images that don't have db relation

[webent]

Hi, could someone please help me with my code, I'm trying to run a script that cleans up any unused images... It loops through the image directory and checks the db each time to see if there is an entry that uses that corresponding image, if not, it deletes it... Here's my code thus far... I put in a couple echo statements to kind of see what it was doing...

$dirname = "/home/username/public_html/supplier/images/";
$pattern="(\.jpg$)"; 
$files = array();
if($handle = opendir($dirname)) {         
    while(false !== ($file = readdir($handle)))	{
        if(eregi($pattern, $file)) {
            $result = mysql_query ("SELECT COUNT(*) AS file_exists FROM products WHERE product_image = '$file'");
            extract ($file_exists = mysql_fetch_assoc($result)); 
            echo $file . ' - ' . $file_exists . '<br />'; // First echo
            if ($file_exists == 0) {
                echo $file . ' - Deleted <br />'; // Second echo
                unlink ($file);
            }                                               
        }                                                
    }
    closedir($handle);
}

 

Here's what I am getting,... keep in mind, there are thousands of images in that directory that should be processed...

 

First echo

958-446186.jpg - 2
1003-447023.jpg - 0

 

Second echo

1003-447023.jpg - Deleted

 

Now as you can see, not very man entries were checked, and not only that, but when I went to check to see if that file had really been deleted, it hadn't... so it would seem that my unlink isn't working either.

[Fadion]

I would go for a bit simpler way:

 

<?php
$dir = '/home/username/public_html/supplier/images/';
foreach(glob($dir . '*.jpg') as $file){ //loop through all the jpg images of the folder
     $results = mysql_query("SELECT id FROM products WHERE product_image='$file'") or die();
     if(mysql_num_rows($results) == 0){ //if the query returned 0 results, the file doesn't exist in the database
          unlink($dir . $file);
          echo $file . ' Deleted<br />';
     } else{ //this will trigger if the query returns results
          echo $file . ' Exists<br />';
     }
}
?>

 

Give it a try and see if it works.

[webent]

I will do, thank you, I'll let you know if it works...

 

Any idea why after it reaches about a couple thousand images, it slows to a crawl? It has just over 20000 images to check, taking forever...

[Fadion]

20.000 images aren't a few actually. If it slows to death and you'll need to run this operation once (or once in a while) split the images into different folders, maybe 1.000 images in one and run the script in each of them. If you need to run this periodically, then design your system to delete images where a db record is deleted or something like that.

[webent]

Well, right now your script is giving me "Internal Server Error 500" ...still sifting through it trying to find any piece of it that may be throwing it... I get that error for every little code infraction, drives me crazy... Used to be with php4, it would at least give you an error to go by...

 

$dir = "/home/username/public_html/supplier/images/";
foreach(glob($dir . "*.jpg") as $file){ //loop through all the jpg images of the folder
     $results = mysql_query("SELECT product_id FROM products WHERE product_image = '$file'") or die();
     if(mysql_num_rows($results) == 0){ //if the query returned 0 results, the file doesn't exist in the database
          unlink($dir . $file);
          echo $file . ' Deleted<br />';
     } else{ //this will trigger if the query returns results
          echo $file . ' Exists<br />';
     }
}

[webent]

I got it fixed, "unlink($dir . $file);" just needed to be changed to "unlink($file);"

[Fadion]

Ohh that's right, glob() will return the full path to the file. My bad. Is it working now?

[webent]

Fortunately I'm testing it on a duplicate directory, because it's deleting all of the images... Once again, good 'ole mysql_num_rows isn't working, so every result == 0 ... I'll see if I can make some mods to it and I'll post back in a bit...

[Fadion]

Ohh there's still the $file problem, as it's checking the database with the full path. I'm sure you have entered in the db only the filename, not the full path. So:

 

<?php
$filename = basename($file); //this will get only the filename, ie: 'file.jpg'
?>

 

The full code should be:

<?php
$dir = '/home/username/public_html/supplier/images/';
foreach(glob($dir . '*.jpg') as $file){ //loop through all the jpg images of the folder
     $filename = basename($file);
     $results = mysql_query("SELECT id FROM products WHERE product_image='$filename'") or die();
     if(mysql_num_rows($results) == 0){ //if the query returned 0 results, the file doesn't exist in the database
          unlink($file);
          echo $filename . ' Deleted<br />';
     } else{ //this will trigger if the query returns results
          echo $filename . ' Exists<br />';
     }
}
?>

 

Sorry for the confusion, i should have tried it.

[webent]

LOL... NP, I should have seen that, "$filename = basename($file);"

 

I had put this together just before you replied... Well, without the basename function that is...

$dir = "/home/username/public_html/supplier/images/";
foreach(glob($dir . "*.jpg") as $file){ //loop through all the jpg images of the folder
$filename = basename($file);
     $results = mysql_query("SELECT product_image FROM products WHERE product_image = '$filename'") or die();
        while ($row = mysql_fetch_assoc($results)) {
            $product_image = $row['product_image'];
        }
        if (empty($product_image)) {          
            unlink($file);
            echo $file . ' - Deleted <br />'; 
        }
}

 

Do you think it matters which one I use, one faster or better than the other?

[Fadion]

I would suggest using mysql_num_rows() instead of fetching the returned data in an array (thus creating one) and checking if it's empty. It will work in each case anyway.

[webent]

Good enough for me, thanks for all of your help, I appreciate it...