How to Delete all Unused photos Function

Question

As a rule, I suggest you try to stick as much as possible to WordPress core functions when modifying the database, as they will handle associated data automatically – e.g. delete associated post meta, associated taxonomy info, etc.
(Woocommerce stores product image data as product post_meta, see below for details.) This might slow things down in comparison to direct access to the database, but the database will be much cleaner as a result.

Needless to say, do backup your database before trying any of this…

First, you need to work with post ids.

global $wpdb;
foreach ($old_product_list as $old_product) {
   $prod_names[] = $mysqli->real_escape_string($oldproduct['name']);
}

$prod_ids = $wpdb->get_results($wpdb->prepare("
                 SELECT ID FROM {$wpdb->prefix}posts 
                 WHERE post_type="product" AND post_title IN (%s)",
                 explode(',',$prod_names)));

Now, there are three different things you may want to delete, in increasing order of ‘destructiveness’:

Delete the references to image ids in the products post_meta
Delete the ‘attachment’ posts associated to each image – this will remove
the images from WordPress media Gallery
Delete the actual image files from the server

Point 1. This one is easy. But to do it right, you need to use wordpress functions, as the code comments hopefully make clear.

foreach ($prod_ids as $id) {
    // This will also remove all post_meta associated to the product
    // and apply any delete_post filters added by Woocommerce or other plugins
    // Second arg forces deletion, bypassing the trash
    wp_delete_post($id,true); 
}

Points 2 and 3, simple case.

If your images are all unique to each product and not linked from anywhere, their post_parent will be the product_id, so you can use this version of https://wordpress.stackexchange.com/a/109803/40965, modified to bypass the trash:

function delete_product_attachments($id) {
    if ('product' !== get_post_type($id)) return;

    $media = get_children(array(
        'post_parent' => $id,
        'post_type' => 'attachment'
    ));

    foreach ($media as $img) {
        unlink(get_attached_file($img->ID)); //delete files from server
        wp_delete_attachment($img->ID,true); //force post deletion, bypassing trash
    }
}

add_action('before_delete_post','delete_product_attachments');

The last line makes sure that this function is called whenever wp_delete_post is called, so you don’t need to change the solution to point 1 above.

Points 2 and 3, general case. If some images are shared among products (some of which are perhaps not deleted in the current batch), or linked from other posts, the above solution will break things.

Here is where we need to understand how Woocommerce handles product images. The images associated to a product are stored as post_meta ‘_thumbnail_id’ and ‘_product_image_gallery’ for the product’s post ID. _thumbnail_id is the post_id of the image (post with post_type=”attachment”); _product_image_gallery is a comma-separated string of image post ids.

Just to get you started, here is how to get all the attachment post ids to handle.

$image_ids = $wpdb->get_col($wpdb->prepare("
                 SELECT meta_value FROM {$wpdb->prefix}post_meta
                  WHERE meta_key = '_thumbnail_id' AND post_id in (%s)",
                 explode(',',$prod_ids)));
$gallery_ids = $wpdb->get_col($wpdb->prepare("
                 SELECT meta_value FROM {$wpdb->prefix}post_meta
                  WHERE meta_key = '_product_image_gallery' AND post_id in (%s)",
                 explode(',',$prod_ids)));

$gallery_ids = explode(',',$gallery_ids); //get all ids in a single string
$gallery_ids = array_unique(implode(',',$gallery_ids));
$image_ids = array_merge($image_ids,$gallery_ids);
//do something with all $image_ids

And here comes the hard part: apply the solution above only to images that can be safely deleted.

Related Posts: