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');
$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
| 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):

        wp_posts p1
        wp_postmeta wm1
        ON (
            wm1.post_id = p1.id 
            AND wm1.meta_value IS NOT NULL
            AND wm1.meta_key = "_thumbnail_id"              
        wp_postmeta wm2
        ON (
            wm1.meta_value = wm2.post_id
            AND wm2.meta_key = "_wp_attached_file"
            AND wm2.meta_value IS NOT NULL  
        AND p1.post_type="post"
        p1.post_date DESC
| improve this answer | |
  • Not exactly what was asked, but for me this answer is more useful.. thanks! – vicenteherrera Oct 29 '14 at 20:18

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