0

I'd like to remove gallery images only (not featured image) from sold products on my woocommerce site in bulk because my server storage limits are being reached. I have nearly 2000 out of stock products, so doing this manually is not an option. I do not want to delete the products or featured image, only the gallery images.

Just to get something working quickly I tried to create an sql query that would remove the product ID from wp_posts.post_parent for just the product gallery attachments but my query isn't working. The idea is if the query can unattach the images, then I can go into the media library and delete all unattached images in bulk which should also delete the physical file from the server.

Here is my attempt, hopefully someone can look at this and see the problem:

UPDATE
    wp_posts
  inner join 
    wp_postmeta on find_in_set(wp_posts.ID, wp_postmeta.meta_value)
  SET 
    wp_posts.post_parent = ''
  WHERE
    wp_postmeta.meta_value = 'outofstock'
    AND
    wp_postmeta.meta_value = wp_posts.post_parent
    AND 
    wp_posts.post_type = 'attachment'
    AND 
    wp_posts.comment_status = 'open';

Thanks!

0

I know this is kind of an old post, but I was looking for the exact same thing. Your post got me going in the right direction, and I finally came up with a working solution. I run the following SQL, and then I have WP-Optimize Premium to delete unused images.

The script itself does basically what you outlined. I first create a temp table of items that are out of stock but have a value for the image gallery. I then 'unattach' all of the images except the featured image. Finally, I go back and remove the value from the meta table for the gallery images.

I ran this for the first time yesterday and cleared 1.5 GB from my hosting server.

(Just as a side note, I use WP-Lister Pro for eBay in my WP setup, so I don't know if that affects the location of any of these fields or if this is the standard image linking.)

CREATE TEMPORARY TABLE outofstock_cleanup
SELECT meta.post_id
FROM wp_postmeta meta
JOIN wp_postmeta meta2 on meta.post_id = meta2.post_id AND meta2.meta_key = '_product_image_gallery'
AND meta2.meta_value <> ''
WHERE meta.meta_key = '_stock_status' AND meta.meta_value = 'outofstock';

UPDATE wp_posts, wp_postmeta
SET wp_posts.post_parent = ''
WHERE
wp_postmeta.post_id = wp_posts.post_parent AND wp_postmeta.meta_key = '_thumbnail_id'
AND wp_posts.post_type = 'attachment'
AND wp_posts.comment_status = 'open'
AND wp_posts.post_parent IN (SELECT post_id FROM outofstock_cleanup)
AND wp_posts.id <> wp_postmeta.meta_value;

UPDATE wp_postmeta
SET meta_value = ''
WHERE meta_key = '_product_image_gallery'
AND post_id IN (SELECT post_id FROM outofstock_cleanup);

SELECT COUNT(*) FROM outofstock_cleanup;

DROP TEMPORARY TABLE outofstock_cleanup;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.