check filesystem file consistency against db

Question

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

Answer 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}");
}