0

I have a PHP script that uploads files to a directory on my filesystem. On the same time I store the filename in a field on a MYSQL DB.

I have seen that I have more files than records in the db.

What's the shortest way to find out and delete all those files that are not linked to a record on the DB? My idea would be to read each filename from the directory, run a query on the db with the filename taken from step 1 and in case the query returns 0 result to delete the file.

Thanks in advance for any advice.

Lelio

Parixit
  • 3,829
  • 3
  • 37
  • 61
Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
  • Fetch all filenames from DB into set A, fetch all filenames from the filesystem into set B, delete the ones from set B that are not present in set A. – DCoder Sep 01 '13 at 17:54
  • http://php.net/manual/en/function.scandir.php check here and also here http://php.net/manual/en/function.readdir.php – Parixit Sep 01 '13 at 17:54

1 Answers1

1

Get all the files in the folder using scandir, and use the NOT IN clause to remove the files not in the folder.

$files = implode(scandir('files/'), "', '");
$db->query("DELETE FROM files WHERE file_name NOT IN('{$files}')");

If you want to do the opposite and delete the files which don't link up to a record in the database you can use a function called unlink. This will delete the file. There's probably more optimized versions, but this is what I would do:

$files = implode(scandir('files/'), "', '");
$sth = $db->query("SELECT * FROM files WHERE file_name NOT IN('{$files')");

foreach($sth->fetchAll(PDO::FETCH_OBJ) as $file) {
    unlink("files/{$file_name}");
}
Jordan Doyle
  • 2,976
  • 4
  • 22
  • 38
  • Thanks! Didn't know about implode, I'll give it a try. Only one more question: as far as I understand the foreach loop will delete those files that are both in the db and in the filesystem or I am missing something?? – Lelio Faieta Sep 02 '13 at 07:42
  • It will list all the files in the folder, if one of them is `NOT IN` the database it will delete it. There was never a record in the DB to delete. – Jordan Doyle Sep 02 '13 at 12:02