Query for Missing Featured Images

I upgraded a site and have many missing featured images. I 've tried a few plugins that are supposed to set the first uploaded image to be "featured" if the featured image wasn't manually set, but so far, I have not had any luck with using a plugin to do so.

My question is: Could anyone tell me if there is a query I can run on the backend of my wordpress database using mysql directly that would allow me to see how many of my existing posts are missing the featured image setting? This way I could determine whether or not I can just go in and do this manually, or if I need to come up with a different option. Thank you!

  • aecnu

    Greetings sitesonline,

    Thank you for this great item to bring up, it is greatly appreciated.

    I upgraded a site and have many missing featured images.

    What type of upgrade was this?

    It would be good to know for other members.

    I 've tried a few plugins that are supposed to set the first uploaded image to be "featured" if the featured image wasn't manually set, but so far, I have not had any luck with using a plugin to do so.

    I hear that, the featured image is a tough one at that and i have not found a suitable way to do this either except manually adding them.

    Could anyone tell me if there is a query I can run on the backend of my wordpress database using mysql directly that would allow me to see how many of my existing posts are missing the featured image setting?

    This is truly wishful thinking in which I commend you for, but no there is not because you cannot query nothingness so to speak.

    Thank you for being a WPMU Dev Community Member!

    Cheers, Joe

  • Paul

    @sitesonline,

    Sorry, @aecnu, you can actually query for nothingness. Here is a simple SQL statement you can run using phpMyAdmin or whatever means directory against your WordPress MySQL system.

    Some assumption on the table name prefix ( wp_) and the query of 'posts' only. Feel free to change these.

    SELECT p.ID, p.post_title, pm.* FROM wp_posts p
    LEFT JOIN wp_postmeta pm ON p.ID=pm.post_id AND pm.meta_key='_thumbnail_id'
    WHERE p.post_type in ('post':wink:
    AND p.post_status in ('publish':wink:

    This SQL will join the wp_posts table items with the wp_postmeta The result will look something like the following image. The row showing 'null' are the posts without a featured image.

    More details on this. When you set the featured image on a post WordPress internally sets a value in wp_postmeta for the meta_key '_thumbnail_id'.

  • sitesonline

    Paul, So I ran your query---and it gave me exactly the info I needed.

    But I have hundreds of posts showing null values in the fields you mentioned. Sigh :slight_frown:

    Is there by any chance a simple addition to your query that would be able if I ran it directly in phpmyadmin just set the featured image to the first image in the post, or is my only option doing this manually?

    If there isn't such a simple workaround, how difficult do you think this would be to code? And how long of a project (in hours) do you think it would take someone who was familiar with wordpress' database structure to do so.

    Again, thank you for the help, at least now I have more info about how many posts I have to content with. I actually like working in the backend using mysql queries better than using plugins so if there was a way to do this with a query or two, that'd be just great.

  • Paul

    @sitesonline, Unfortunately there is not a quick and easy way to do this via phpMyAdmin. This would involve multiple queries but is not complicated. Should take a person no longer than 2 hours to code.

    Have you tried looking for a plugin solution. Just a quick google I came across this plugin http://wordpress.org/extend/plugins/auto-post-thumbnail/

    Disclaimer: I've in no way tested or used this plugin. Just read the description and might work for you.

  • Tom Eagles

    Greetings and thanks for being a great community member.
    We haven't heard from you on this one for long and I am doing a regular followup to see if there is still something we can assist you on this thread.
    Just to manage the support issues more efficiently, I am marking this thread as resolved for now however this is not being done to avoid your questions in any way.
    Please feel free to mark this is "Not resolved" in case you have further questions and we would be back on it.
    Thanks a lot for being with WPMU DEV.
    Cheers!
    Tom
    Sales &Support
    Did you know we offer FREE lifetime memberships? Click here to learn more.

  • sitesonline

    Thank you so much Paul and Tom. I was finally able to get it fixed by using Paul's suggestion as well as the code/fix from this page. Hopefully this is ok to post the link so that in the event someone else has this trouble, they can also get their thumbnails to work. http://www.clickthrough-marketing.com/how-to-fix-auto-post-thumbnail-on-wordpress-3.4-seo-friendly-800610805/

    Thanks again for helping me out! Appreciate it.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.