Find WooCommerce products with missing images (SQL)

When you’re working on a WooCommerce site with loads of products and you know there are some with missing images it’s a drag to have to go through the product list to visually identify which ones they are. So how do you more easily find the products with missing images? If you have access to the database, a much easier way of doing it is to run a little SQL snippet.

Products are located in the wp_posts table with a post_type of ‘product’. Also, you probably only want to get the products that are published, so the post_status for this is ‘publish’.

Products that have featured images set have a corresponding record in the wp_postmeta table with a meta_key of ‘_thumbnail_id’ so we are targeting ones that don’t. We can use NOT EXISTS for this. So you can find these products with missing images by running this script:

SELECT  p.ID,p.post_title
FROM    wp_posts p 
WHERE   p.post_type = 'product' 
AND     p.post_status = 'publish'
AND     NOT EXISTS (SELECT '1' 
                    FROM    wp_postmeta pm 
                    WHERE   pm.post_id = p.ID 
                    AND     pm.meta_key = '_thumbnail_id' )

Getting the data as links to product page

If you want to get the data as full links to the admin pages for these products you can use the following in the select clause:

<yoursiteurl>/wp-admin/post.php?post=<p.ID>&action=edit

So if my site was example.com, the select would look like this:

SELECT  CONCAT('https://example.com/wp-admin/post.php?post=',p.ID,'&action=edit')
FROM    wp_posts p 
WHERE   p.post_type = 'product' 
AND     p.post_status = 'publish'
AND     NOT EXISTS (SELECT '1' 
                    FROM    wp_postmeta pm 
                    WHERE   pm.post_id = p.ID 
                    AND     pm.meta_key = '_thumbnail_id' )

You can paste the output straight into the browser and it will bring up the edit screens for each of the products.

The script that gets the admin URLs uses MySQLs concat function which is not exactly the same as the way as some other databases concatenate. You can read about it here.