Aaron Rotem Aaron Rotem - 1 year ago 37
MySQL Question

Delete entries from a table and store a value from the deleted entries

I have a table set up with the names of all the files in a directory. I want to be able to delete all the files that have a specific property from both the table and the directory. For example, if the entry does not have a

then I want to delete it. The sql statement I thought about using would be
delete from master where name = null
but then I wouldn't be able to delete the file from the directory because I would have no way to determine which file to delete. Is there a way to store the values from the entries in the table in an array before I delete them?

Answer Source

Do a select first:

SELECT * FROM master WHERE name = null

Then store the results in a variable and you can delete the files with unlink($fileName).

If you want to do it sql speed efficiently, go for your

DELETE FROM master WHERE name = null

however, I personally would loop through the files and do it individually like this:

    $fileNames = your_sql_select_function("SELECT filename FROM master WHERE name = null");
    foreach($fileNames as $fileName) {
        if(unlink($fileName)) {
            your_sql_delete_function("DELETE FROM master WHERE filename = '$fileName'");            
        else {
            // error report, do not delete it from database yet
            // if you delete it, you won't be able to get the name later 
            // and the file will just hang in there until you 
            // handle it some other way

Of course you have to substitute your_sql_select_function and your_sql_delete_function for the functions you would use to handle mysql