WordPress delete all post attachments if not used on any other page

Question

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.

Answer 1

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