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 url name
- Featured image
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
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