Skip to content
DATABASE PHP

How to get post thumbnail URL by SQL query in WordPress

A
Author
Jun 27, 2020 · 5 min read
How to get post thumbnail URL by SQL query in WordPress

In WordPress, retrieving the featured image (also known as the post thumbnail) is an essential part of displaying content. By default, WordPress stores the featured image (thumbnail) URL in the database as a post meta value. If you’re working with custom queries, custom themes, or building your own solutions, there are times when you may need to retrieve the thumbnail URL of a post directly via SQL queries. This blog will walk you through the process of extracting the post thumbnail URL using SQL queries, allowing you to efficiently retrieve image data without relying on WordPress functions.


Understanding WordPress Post Thumbnails

Before diving into the SQL query, let’s take a moment to understand what a post thumbnail is and how it is stored in WordPress.

What is a Post Thumbnail?

A post thumbnail in WordPress is essentially the featured image of a post or page. It is commonly used to represent the content visually on the front end of the website. The post thumbnail can appear in places like:

  • Blog post listings
  • Homepages
  • Category pages
  • Archives
  • Widgets
  • And more

This image is usually selected from the WordPress editor when creating or editing a post. By default, WordPress stores the thumbnail’s ID in a post’s metadata (_thumbnail_id), and the actual image’s path can be retrieved by accessing the relevant post meta.


How WordPress Stores Post Thumbnails in the Database

WordPress stores a variety of metadata related to posts in a set of tables within the database. The main tables that are relevant for post thumbnails are:

  1. wp_posts: This table contains information about the posts, including post title, content, and status.
  2. wp_postmeta: This table contains the metadata for posts, including the thumbnail ID (stored as _thumbnail_id).
  3. wp_term_relationships: This table is used to link posts to categories, tags, or custom taxonomies.
  4. wp_terms: This table stores the actual terms (categories or tags) that posts belong to.

To retrieve a post’s thumbnail, we primarily need to access the _thumbnail_id meta key in the wp_postmeta table, which stores the attachment ID of the image. Then, we need to use this ID to retrieve the image file path, typically stored in the wp_postmeta table with the key _wp_attached_file.


Building the SQL Query to Get the Post Thumbnail URL

Now, let’s dive into the SQL query that will allow you to retrieve the post thumbnail URL directly from the database. WordPress uses multiple tables to store post information, including post metadata, which we can leverage to get the image URL.

The SQL Query Breakdown

Below is a detailed SQL query to retrieve the post thumbnail URL along with other post-related data:

SELECT 
    post.ID,
    post.post_title,
    post.post_content,
    post.post_date,
    post.category_name,
    post.category_slug,
    post.category_id,
    CONCAT( 'https://mycodingsolution.com/wp-content/uploads/', thumb.meta_value) AS thumbnail,
    post.post_type
FROM (
    SELECT  
        p.ID,
        p.post_content,
        p.post_title, 
        p.post_date,
        p.post_type,
        MAX(CASE WHEN pm.meta_key = '_thumbnail_id' THEN pm.meta_value ELSE NULL END) AS thumbnail_id,
        term.name AS category_name,
        term.slug AS category_slug,
        term.term_id AS category_id
    FROM wp_posts AS p 
    LEFT JOIN wp_postmeta AS pm ON (pm.post_id = p.ID)
    LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
    LEFT JOIN wp_terms AS term ON tr.term_taxonomy_id = term.term_id
    WHERE p.post_status = 'publish'
    GROUP BY p.ID 
    ORDER BY p.post_date DESC
) AS post
LEFT JOIN wp_postmeta AS thumb 
    ON thumb.meta_key = '_wp_attached_file' 
    AND thumb.post_id = post.thumbnail_id
ORDER BY post.post_date DESC 
LIMIT 3;

Understanding the Query’s Output

The output of this SQL query will return the following columns for each post:

  1. ID: The ID of the post.
  2. post_title: The title of the post.
  3. post_content: The content of the post.
  4. post_date: The date when the post was published.
  5. category_name: The name of the category the post belongs to.
  6. category_slug: The URL-friendly version of the category name.
  7. category_id: The ID of the category the post belongs to.
  8. thumbnail: The URL of the post’s thumbnail image.
  9. post_type: The type of post (e.g., post, page, custom post type).

This will provide you with all the necessary information, including the URL of the featured image for each post. In 2025, we can all experience more powerful and flexible Next-gen VPS hosting, which can offer reliable virtual private server options.


Advantages of Using SQL Queries to Retrieve Thumbnail URLs

While WordPress provides functions like get_the_post_thumbnail_url() to retrieve thumbnail URLs, there are certain scenarios where executing custom SQL queries directly on the database can be advantageous:

  1. Custom Queries: When you need to retrieve multiple pieces of information from the database and want to customize your query, SQL queries provide more flexibility and control.
  2. Performance: Direct SQL queries can be faster in certain situations as they bypass the overhead of WordPress functions, especially if you need to retrieve large sets of data in a custom format.
  3. Advanced Customization: If you have specific needs that cannot be easily achieved with WordPress functions, raw SQL queries give you the power to craft highly customized solutions.

Conclusion

Retrieving the post thumbnail URL using SQL queries in WordPress can be a powerful technique for customizing your site and improving performance. By understanding how WordPress stores thumbnails in its database and using SQL to access the data, you can create dynamic and optimized queries tailored to your site’s needs.

Whether you are building a custom theme, plugin, or a unique project, this method offers a direct and efficient approach to obtaining the featured image URL of posts without relying solely on WordPress’s built-in functions.

By utilizing SQL, you gain complete control over how you retrieve and present the thumbnail URLs, making it a versatile solution for advanced WordPress development.

Note: Please note that using SQL queries directly can be risky and it’s recommended to use WordPress functions and APIs instead for better security and compatibility.

A
Author

Author at The Creative Coders.

The monthly build log

One email a month. New posts, lessons, and the occasional strong opinion. No spam.