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:
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.