DEV Community

oka
oka

Posted on

MySQL Query to Extract WordPress Posts with Featured Thumbnails

#wordpress #mysql #phpmyadmin #frontend

Recently, I helped a friend migrate content from WordPress to Shopify.
The default WordPress export tool didn’t quite meet our needs, and due to an older version of WordPress, we couldn't install new plugins either. That made it quite a challenge to export the post data properly.

What surprised me the most was that WordPress's default export didn’t include featured image data (thumbnails).
Of course, since we were migrating blog posts, we wanted the thumbnail data to be tied to the articles as well.

So, I figured — why not just write a SQL query?
It worked well, so I'm sharing the query and an explanation of how it works.

✅ Final Query 

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content,
  CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
Enter fullscreen mode Exit fullscreen mode

Let me walk you through how I built this query.

🧱 WordPress Database Tables Involved

WordPress stores data across around 12 tables.
For this task, we’ll focus on the following two:

Table Description
wp_posts Stores post data
wp_postmeta Stores post metadata

(Source: https://codex.wordpress.org/Database_Description)

🗂️ wp_posts Table

Column Description
ID Unique post ID (Primary Key)
post_title Post title
post_name Slug (used in URL)
post_content Post content
post_type Post type (post, page, custom_post_type, etc.)
post_status Post status (publish, draft, etc.)
post_date Publish date/time

🗃️ wp_postmeta Table

Column Description
meta_id Metadata ID
post_id Related post ID (from wp_posts.ID)
meta_key Metadata key (e.g., _thumbnail_id)
meta_value Metadata value (e.g., image ID or path)

🛠️ Planning the Output Format

We wanted something like this:

id title date content thumbnail_url
101 Sample Title 2024-05-01 12:00:00 (Post body) https://example.com/wp-content/uploads/2024/05/sample.jpg

🧪 Step-by-Step Query Breakdown

Step 1: Start with basic post data

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content
FROM wp_posts p
Enter fullscreen mode Exit fullscreen mode

Then filter to only published blog posts:

WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
Enter fullscreen mode Exit fullscreen mode

To filter custom post types, replace 'post' with your custom slug, e.g.:

WHERE p.post_type = 'work'
Enter fullscreen mode Exit fullscreen mode

Step 2: Get the Thumbnail ID 

LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
Enter fullscreen mode Exit fullscreen mode

This gives us a result like:

id title thumbnail_id
101 Sample Post A 321
102 Sample Post B 322

Step 3: Get the File Path of the Image

Next, we take the thumbnail ID and use it to find the file path from _wp_attached_file

LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
Enter fullscreen mode Exit fullscreen mode

Step 4: Build the Full URL

Finally, we concatenate the base path with the file name:

CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
Enter fullscreen mode Exit fullscreen mode

🎉 Final Query (Again for Reference) 

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content,
  CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
Enter fullscreen mode Exit fullscreen mode

📝 Conclusion

While AI can generate SQL for you quickly, understanding why and how it works is super important — especially when dealing with systems like WordPress that have specific data structures.

I don’t write raw SQL very often at work, but I’d like to keep growing in this area step-by-step!

Top comments (0)