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