1

How do I search the database to find out which articles are missing a featured image on them? I looked around in the wp_posts table and searched post_type is equal to article. It gives me a list of all articles but the guid filed has images and no way to see which ones are empty. Does anyone know how I can achieve this?

2

I guess you want it to know in which article you need to edit and add featured image.

You can create some file in the worpdress root folder and just use WP_Query to get all the posts and to check if there is no feature image you can use the has_post_thumbnail().

Change the post_type if needed.

Example

<?php
require_once('./wp-load.php');
$query = new WP_Query(array('post_type'=>'post'));
if ( $query->have_posts() ) while ( $query->have_posts() ) : $query->the_post();
    if(has_post_thumbnail() === false) {
    ?>
    <a href="<?php the_permalink(); ?>"><?php the_title(); ?></a><br>
    <?php
    }
endwhile;

If you still want to use the SQL you can create WP_Query and take the generated SQL. You can also use this WP_Query like the first method and you don't need to check if has_post_thumbnail() is false.

$query = new WP_Query(
    array(
        'post_type'=>'post',
        'meta_query' => array(
            array(
                'key' => '_thumbnail_id',
                'compare' => 'NOT EXISTS'
            )
        )
    )
);
echo ($query->request); // Print the SQL

So the SQL for this example after I modify it is a little.

SELECT * FROM wp_posts  LEFT JOIN wp_postmeta ON
(wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_thumbnail_id' )
WHERE 1=1  AND (wp_postmeta.post_id IS NULL) AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date
  • Thanks, is there a way to do it in the database though? – MikeL5799 Dec 20 '17 at 19:08
  • @MikeL5799 see the answer now – Shibi Dec 20 '17 at 19:44
  • Thanks so I just ran the query in sql and nothing comes up. my post type is called expert advice. Where would I change that? where wp_posts.post_type = 'post' correct? – MikeL5799 Dec 20 '17 at 20:05
  • Yes. You also need to change the table prefix if it's other than wp_ better use the second wp_quey first and get the correct SQL for you – Shibi Dec 20 '17 at 20:09
  • I have been looking to find posts with missing featured images, but didn't want to create any files or query the database. This plugin shows featured image column in WP - All Posts. It's easy to scroll down or Ctrl+F and look for "Set featured image". – rluks Aug 1 '18 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.