How to get post thumbnail URL by SQL query in WordPress

In WordPress, the post thumbnail URL is stored as a meta value with a meta key of _thumbnail_id. The _thumbnail_id meta value is a reference to the attachment post ID that is associated with the post thumbnail.

To get the post thumbnail URL by SQL query in WordPress, you can use a combination of the wp_postmeta and wp_posts tables. Here’s an example SQL query that you can use:

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 blog_posts as p 
    LEFT JOIN blog_postmeta as pm ON ( pm.post_id = p.ID)
    LEFT JOIN blog_term_relationships as tr ON tr.object_id = p.ID
    LEFT JOIN blog_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 blog_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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *