Wordpress mysql query for featured image also

Question

So I have the following query which works but I also need the posts featured image, can you guys help me out to query for that too.

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts, wp_term_relationships, wp_terms WHERE wp_posts.ID = wp_term_relationships.object_id AND wp_posts.post_status = 'publish' AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0,100

mayersdesign mayersdesign · Accepted Answer · 2017-04-08T13:53:25

I recently had to retrieve the latest 4 posts with featured images, and found it really quite difficult. This code worked for me (in the end) I know it may not be exact, but its impossible to post in a comment, and I am sure it will help:

SELECT title, name, date, content, CONCAT(LEFT(image, LENGTH(image) - LOCATE('.', REVERSE(image))),'-768x576.',SUBSTRING_INDEX(image, '.', -1)) AS image
            FROM (
             SELECT    
             p.post_title AS title, 
             p.post_status AS 'status', 
             p.post_date AS date,
             p.post_content AS content,
             p.post_name AS name,
             (SELECT `guid` FROM wp_posts WHERE id = m.meta_value) AS image
             FROM wp_posts p, wp_postmeta m
             WHERE p.post_type = 'post'
             AND p.post_status = 'publish'
             AND p.id = m.post_id
             AND m.meta_key = '_thumbnail_id'
             LIMIT 4
            ) TT

Wordpress mysql query for featured image also

1 Answers