Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I know you can use the wordpress methods for getting a featured image, however with this particular project I need to get the featured image via mysql query. Can anyone point me in the right direction. Thank you.

This is what I have so far but my query is not doing the trick. I have the $post->id stored as a variable called $da_id

            $Featured_image = $wpdb->get_results("
            SELECT *
            FROM net_5_postmeta  
            INNER JOIN net_5_posts ON net_5_postmeta.post_id=net_5_posts.ID 
            WHERE post_parent = $da_id
            AND meta_key = '_wp_attached_file' 
            ORDER BY post_date 
            DESC LIMIT 15",'ARRAY_A');
share|improve this question

2 Answers 2

up vote 2 down vote accepted
$Featured_image = $wpdb->get_results("
    SELECT p.*
      FROM net_5_postmeta AS pm
     INNER JOIN net_5_posts AS p ON pm.meta_value=p.ID 
     WHERE pm.post_id = $da_id
       AND pm.meta_key = '_thumbnail_id' 
     ORDER BY p.post_date DESC 
     LIMIT 15
",'ARRAY_A');
share|improve this answer
    
Eugene you saved my life, it works like a dream, THANK YOU! –  uknowit2 Apr 13 '12 at 12:18
    
You are welcome! Mark right answer and vote it up. –  Eugene Manuilov Apr 13 '12 at 12:37
    
thanks again Eugene I marked as correct but unfortunately don't have enough reputation to vote up but I'm sure this will help others. the code above is particularly useful in an MU environment when you need to query featured images of other blogs. I love it. Now I just need to work out how to now bring in the thumbnail version of the featured image. –  uknowit2 Apr 14 '12 at 11:05

A related solution, to query for posts WITHOUT providing a post ID (ordered by post date, and using the wp_ database prefix):

    SELECT
        p1.*,
        wm2.meta_value
    FROM 
        wp_posts p1
    LEFT JOIN 
        wp_postmeta wm1
        ON (
            wm1.post_id = p1.id 
            AND wm1.meta_value IS NOT NULL
            AND wm1.meta_key = "_thumbnail_id"              
        )
    LEFT JOIN 
        wp_postmeta wm2
        ON (
            wm1.meta_value = wm2.post_id
            AND wm2.meta_key = "_wp_attached_file"
            AND wm2.meta_value IS NOT NULL  
        )
    WHERE
        p1.post_status="publish" 
        AND p1.post_type="post"
    ORDER BY 
        p1.post_date DESC
share|improve this answer
    
Not exactly what was asked, but for me this answer is more useful.. thanks! –  vicenteherrera Oct 29 at 20:18

protected by Community Jan 10 '13 at 4:11

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.