1

I've looked all over and am unable to find any code relating to my query.

I essentially want to programmatically delete images, however, my issue is that some of the images used on these posts have been used on other posts and naturally deleting them would break those images.

I am doing this inside a foreach loop over get_posts() the code below is all I have which deletes the attached featured image of the post, what I would like from an answer is:

  1. Deletes all attached media if it be via ACF Image Field or Featured Image
  2. But only delete these if no other post has these images present in their attachment list

From what I've read the solution may sit in an SQL query followed by a count? But I've little experience with SQL to create a working answer unfortunately.

    if( has_post_thumbnail( $event->ID ) ) {
        $attachment_id = get_post_thumbnail_id( $event->ID );

        wp_delete_attachment($attachment_id, true);
    }

I've so far managed to adapt some code I've found for an MySQL query but 2 of the parts I add seems to crash the MySQL Server...

Working code that returns the ID:

SELECT i.ID FROM wp_posts i 
WHERE i.post_type = 'attachment'
AND i.ID = '10038' 
AND i.post_parent > 0 
AND EXISTS (SELECT * FROM wp_posts p WHERE p.ID = i.post_parent)
AND NOT EXISTS (SELECT * FROM wp_postmeta pm WHERE pm.meta_key = '_product_image_gallery' AND pm.meta_value LIKE CONCAT('%', i.ID ,'%'))
AND EXISTS (SELECT * FROM wp_postmeta pm WHERE pm.meta_key = '_thumbnail_id' AND pm.meta_value = i.ID)

Broken code that crashes the server:

AND NOT EXISTS (SELECT * FROM wp_posts p WHERE p.post_type <> 'attachment' AND p.post_content LIKE CONCAT('%', i.guid,'%'))
AND NOT EXISTS (SELECT * FROM wp_postmeta pm WHERE pm.meta_value LIKE CONCAT('%', i.guid,'%'))

I believe if I can ensure the broken code lines are remedied I will be able to get the code working.

Daniel Vickers
  • 1,054
  • 1
  • 12
  • 32

1 Answers1

0

You can have a look at the media library and choose from dropdown all images that aren't attached.

Media Library Unattached

IT goldman
  • 14,885
  • 2
  • 14
  • 28
  • I am aware but as per my post I am trying to do this programmatically on a CRON job – Daniel Vickers Sep 15 '22 at 08:45
  • This is not a simple task. Have a look at [this answer](https://wordpress.stackexchange.com/questions/364025/sql-how-to-find-all-attachments-that-are-not-used-in-any-posts-pages-custom-pos) – IT goldman Sep 15 '22 at 08:55
  • I've read this post this morning and the 2nd answer seems to be the start of what I need but I wish to check for a specific attatchment_id and if it's used in more than 1 location delete im reading up on SQL this morning to try to figure it out but it's a difficult task! – Daniel Vickers Sep 15 '22 at 09:00