Getting WordPress article thumbnails using SQL

I recently did a project where I needed to get post featured images from a WordPress site
using MySQL. Of course I wanted it to be efficient, which meant I had to do with
just a single SQL query to get the information I wanted.

So, here’s the query I came up with:

SELECT p.post_title,p.post_name,meta2.meta_value
FROM wp_posts p
LEFT JOIN wp_postmeta meta1 ON (meta1.post_id = p.id AND meta1.meta_value IS NOT NULL AND meta1.meta_key = '_thumbnail_id')
LEFT JOIN wp_postmeta meta2 ON (meta1.meta_value = meta2.post_id AND meta2.meta_key = '_wp_attached_file' AND meta2.meta_value IS NOT NULL)
WHERE p.post_status='publish'
AND p.post_type='post'
ORDER BY p.post_date DESC
LIMIT 10

This query returns 3 pieces of information:

  • Post title post_title
  • Post url name post_name
  • Featured image meta_value

To get a usable URL from the post_name you have to prepend site base URL and take into account your site’s permalink structure.

For example, if your permalink structure looks like http://example.com/%postname%/ and your post is called new-announcement, your resulting URL would be http://example.com/new-announcement

 

The same thing happens with the featured image. You get something like 2017/09/image.jpg from your query. To get the full URL, prepend your blog name + wp_uploads directory path.

The resulting URL would be http://example.com/wp-content/uploads/2017/09/image.jpg

Leave a Reply...