I'm trying to get the file locations of all featured images used in draft type posts via SQL and phpmyadmin.

posts type attachment should hold the location of the image in the guid column.

SELECT voybp_posts.guid
FROM voybp_posts
WHERE voybp_posts.ID IN (
    SELECT voybp_postmeta.meta_value
    FROM voybp_postmeta
    WHERE voybp_postmeta.post_id IN (
        SELECT voybp_posts.ID
        FROM voybp_posts
        WHERE voybp_posts.post_status="draft"))

This script should be good but its very complex and takes forever to execute, any way for me to simpplify it?

I'm trying to get the file locations of all featured images used in draft type posts via SQL and phpmyadmin.

posts type attachment should hold the location of the image in the guid column.

SELECT voybp_posts.guid
FROM voybp_posts
WHERE voybp_posts.ID IN (
    SELECT voybp_postmeta.meta_value
    FROM voybp_postmeta
    WHERE voybp_postmeta.post_id IN (
        SELECT voybp_posts.ID
        FROM voybp_posts
        WHERE voybp_posts.post_status="draft"))

This script should be good but its very complex and takes forever to execute, any way for me to simpplify it?

• mysql
• sql
• phpmyadmin

1 Answer 1

Alright this worked for me:

First run the bellow script to generate the file locations of all files used for draft posts.

SELECT voybp_posts.guid
FROM voybp_posts
WHERE voybp_posts.ID IN (SELECT voybp_postmeta.meta_value
                         FROM voybp_postmeta
                         WHERE voybp_postmeta.post_id IN (
                             SELECT voybp_posts.ID
                             FROM voybp_posts
                             WHERE voybp_posts.post_status="draft")
                         AND voybp_postmeta.meta_key="_thumbnail_id")

Export this as a CSV file and save it to desktop, then using notepad++ or a similar program use find/replace to add rm to the beginning of every line.

Next SSH into your server and execute the rm commands.

Next you modify the above script and run it to delete the attachment files in the database that lead to nothing.

DELETE
FROM voybp_posts
WHERE voybp_posts.ID IN (SELECT voybp_postmeta.meta_value
                         FROM voybp_postmeta
                         WHERE voybp_postmeta.post_id IN (
                             SELECT voybp_posts.ID
                             FROM voybp_posts
                             WHERE voybp_posts.post_status="draft")
                         AND voybp_postmeta.meta_key="_thumbnail_id")

Finally we find and delete the draft posts.

DELETE
FROM voybp_posts
WHERE voybp_posts.post_status="draft"
AND voybp_posts.post_type="post"

Hope this helps