CODEWITHSUNDEEP
phpmysqlsql
John
John

Reputation: 10156

How to get a list of images to delete from a single MYSQL query

I have a table in the database that keeps track of all images for each main system like so:

Table: system_images
field: systemid
field: imglarge
field: imgthumb

Sometimes a system gets deleted from the database including all records from the system_images that belong to that system entry. However the image itself is still physically on the server. Currently there is a cron job that grabs all the images in the directory, then queries each time to see if that image is still in the table, if not then delete the image off the server. Here is what the current cron job looks like

$system_images = array_diff(scandir($global_productimages),array('..', '.'));
$image_count = count($system_images);

if($image_count > 0)
{

    foreach($system_images AS $curr_image)
    {

        $image_name = trim($curr_image);

        $find = $image_query->findSystemImage($image_name);

        if($find == 0)
        {

            unlink($imgpath .$image_name);

        }

    }

}

Is there a way where I dont have to do a single query for each image? There could be thousands of images in the directory.

Upvotes: 0

Views: 188

Answers (2)

Paul Campbell
Paul Campbell

Reputation: 1986

Two suggestions

Short term (expanding on the suggestion by @paokg4)

You could very easily perform the clean up on the command line with something like

FILEPATH=/global/product/imagedir; mysql -ENe 'select imglarge, imgthumb from `yourdbname`.`system_images` where systemid = 123' | grep -v '^\*' | xargs -i rm $FILEPATH/{}

Where we use mysql's vertical output format (-E) to get both filenames simultaneously while removing column names (-N), suppress the row numbers in the output with a grep, and pipe the results through to rm.

Longer term

It sounds as though the main problem you have here is in the case of removing a 'system' where there might be thousands of associated images - a final tidying up exercise. For this to be a problem it suggests that you store images for multiple systems in a single directory and that there is no obvious way at a file system level of distinguishing between images associated with different systems.

If this does indeed characterise the current setup then it also suggests a couple of longer term solutions to the problem which would allow to remove the files with a single operation e.g.

  1. Store images for different systems in different directories - in which case you can just remove the directory.

  2. Add a system prefix to the filenames you store which would then allow you to delete them with a simple wildcard search.

Upvotes: 0

AlexCode
AlexCode

Reputation: 653

Why dont you delete them right after you delete the system?

1) Before you delete the system record, use a SELECT statement and push in an array the image names that belongs on it

2) Delete the record

3) Unlink the images by iterating through the array which is holding the images

There is no need to use a cron for this job which looks like too heavy if you scan and query the DB one by one.

Upvotes: 2

Related Questions