posts - How do I find which articles are missing a featured image in The WordPress database?

admin管理员组

文章数量:1125974

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?

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?

• posts
• database
• post-thumbnails
• mysql
• sql

Share Improve this question asked Dec 20, 2017 at 16:26 MikeL5799MikeL5799 15511 silver badge1111 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

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

SELECT guid 
FROM wp_posts 
WHERE post_type = "post" 
AND ID NOT IN (
   SELECT post_id as `ID` 
   FROM wp_postmeta 
   WHERE meta_key = "_thumbnail_id"
) 
ORDER BY `wp_posts`.`guid` ASC;

What's happeninng here?

1. Find all the entries in the wp_posts table that are for the post type "Post"
2. Look for all existing post meta entries for the Featured Image, grab me all of those IDs
3. Now filter out the IDs from step 2

The results left over are for posts that don't have a featured image

本文标签： postsHow do I find which articles are missing a featured image in The WordPress database