Compare data in database table and folde, then remove unrelated files from folder

PHP
1

I have a database table with one column holding file names for images.
But the number of images on disk is greater than the number in database (MySQL) table.
Is there a php script which will compare the filenames in the database column with the filenames in a folder on disk and remove the files from disk which do not have an entry in the database table column.

Many thanks

2

This is what I would do:

  1. Grab the filenames from the database
  2. Grab the filenames on disk
  3. Diff the two grabbed lists to make a resulting list
  4. Delete files that appear in resulting list

Explained:

  1. Grab the filenames and put them in to an array. The array format would look something like:

array('file1.txt', 'file2.txt', 'file3.txt', 'file4.txt')
  1. Grab the filenames on disk. A quick, dirty way is to use glob():

$filesOnDisk = glob('/my/path/to/folder/*.txt');
  1. Diff the arrays:

$resultingArray = array_diff($filesInDatabase, $filesOnDisk);
  1. Then delete…

foreach ($resultingArray as $r) {
  unlink($r);
}

Sometimes I like to just echo the filenames and have a quick peek first just to make sure it looks about right - might be worth making a backup before running it. Not a lot of margin for error while testing your script when it’s deleting files :wink:

Good luck!

3

Thanks for the help but it did not work for me.
Don’t know where I went wrong following your instructions??
Here’s the code I have tried. Any help will be appreciated.

$link = mysql_connect(‘localhost’, ‘root’, ‘’);
mysql_select_db(‘testdatabase’);

$sql = "SELECT imagename FROM tbl_image";
$result = mysql_query($sql);

$var = array();

while ($row = mysql_fetch_array($result)) {
	$var[] = $row['imagename'];
}

$filesOnDisk = glob(‘c:\xampp\htdocs\ estfolder\images\*.jpg’);

$resultingArray = array_diff($var, $filesOnDisk);
foreach ($resultingArray as $r) {
unlink($r);
}

4

Following works now: Will delete files from ‘images’ folder if they are not in the database…

//link to mysql server

$link = mysql_connect('localhost', 'root', '');
    mysql_select_db('testdatabase'); 


$sql = "SELECT imagename FROM tbl_image ORDER BY imagename ASC";
$result = mysql_query($sql);

$var = array();

while ($row = mysql_fetch_array($result)) {

	$var[] = $row['imagename'];
}

chdir('images');

    $dir= ('');

    $filesOnDisk = glob($dir."*.*");

   $resultingArray = array_diff($filesOnDisk, $var );

  foreach ($resultingArray as $r) {
        echo $r."<br />";

       unlink($r);

}

5

That sounds pretty scary to me. If the database fails for whatever reason all will be deleted. IMHO you should put in some exception handling or atleast copy() to a temp dir just in case.

6

Definitely. If this is to be a long-term reusable script you need to be really careful - if it’s a one off you can just make sure you have backups I guess, but wouldn’t hurt to be more safe than sorry!

7

I totally agree with your comments. I am learning…

8