Retrieve featured image

1 Views

We are building an Android app based on a WordPress mysql database. We were able to get most of the information from the datase to display on to the app. Now all we have left is to retrieve the posts featured image.

Is there a way to have the featured image output a URL?

Read More

Thanks!

Post Views: 1

Related posts

Leave a Reply

6 comments

  1. The featured image attachment id is stored in the post_meta table, with the meta_key _thumbnail_id.

    Use that post id to find the post meta with meta_key _wp_attachment_metadata.

    The value of that is a serialized array with keys thumb, file, width, height, sizes (an array), and image_meta (an array).

    The file value is relative to the uploads directory for the WP install, and points to the original full-sized image. You should be able to calculate the URL from that.

    sizes has an image size as its key (e.g., ‘small’, ‘medium’, ‘large’, ‘thumbnail’), and the value is an array with keys file, height, and width.

    image_meta will contain things like exif/iptc image metadata from the image itself.

  2. The post thumbnail is a stored in the {$wpdb->prefix}_postmeta table with the key _thumbnail_id.

    Something like this:

    SELECT * from {$wpdb->prefix}_posts 
WHERE ID in (
   SELECT meta_value FROM {$wpdb->prefix}_postmeta 
   WHERE meta_key = '_thumbnail_id'
   AND post_id = ':ID'
);

    Replace :ID with the post id.

    That will return the Post associated with the image itself — the post_title will be whatever the user associated with the title attribute on the image, and the guid will be the full sized image URL.

    If you want different sizes, you’ll have to look up the _wp_attachment_metadata postmeta value which will contain a serialized array with all the different sizes.

    Using WordPress’ api is much easier:

    <?php
if(has_post_thumbnail($some_post_id))
{
   $img = wp_get_attachment_img_src(get_post_thumbnail_id($some_post_id), 'the_image_size');
   // returns an array, the first item is the image URL
   echo $img[0];
}

  3. The post’s featured image is a regular old attachment to the post. So, as with any other image attachment, you’ll need to query the wp_posts table for a post type of attachment. The trick is to first get the attachment ID that you need to grab.

    This is contained in the given post’s meta information, specifically the _thumbnail_id meta.

    So, you’ll need a query that:

    1. Retrieves the value marked by the key _thumbnail_id in the wp_postmeta table based on a given post ID.
    2. Retrieves the attachment from the wp_posts table based on the ID retrieved in step #1
    3. Generates an attachment permalink based on the information retrieved in step #2

    Really, though, you’re doing a lot of work to reinvent the wheel here …

  4. Here’s what I used to retrieve post data, featured image URL and feature image details (such as dimensions):

    SELECT p.*, ( SELECT guid FROM wp_posts WHERE id = m.meta_value ) AS imgurl,  (SELECT meta_value FROM wp_postmeta pm WHERE meta_key='_wp_attachment_metadata' AND pm.post_id=m.meta_value ) AS imgdetails
FROM wp_posts p
LEFT JOIN  wp_postmeta m ON(p.id = m.post_id AND m.meta_key =  '_thumbnail_id' )
WHERE p.post_type =  'post'
AND p.post_status =  'publish' AND p.id=@postId;

  5. Use next query to retrieve the featured images of all posts:

    SELECT result.post_parent, result.guid AS featuredImage
FROM wp_posts AS p 
INNER JOIN wp_postmeta AS pm ON p.ID=pm.post_id
INNER JOIN wp_posts as result ON pm.meta_value = result.ID
WHERE pm.meta_key = '_thumbnail_id' AND p.post_type='post' AND 
p.post_status = 'publish'

  6. The best SQL solution,

    Search the ID from wp_post

    SELECT * FROM wp_posts WHERE post_status='publish'

    Found the meta content

    SELECT * FROM wp_postmeta WHERE meta_key='_thumbnail_id' AND post_id=MyID

    With the thumbnail ID, search one more time into wp_postmeta the file

    SELECT * FROM wp_postmeta WHERE meta_key='_wp_attached_file' AND post_id=MyID

    Here one example.

    $query = mysql_query("SELECT  * FROM wp_posts WHERE post_status='publish' ORDER  BY id DESC LIMIT 6") or die(mysql_error());
    $json='{ "Blog" : [ ';
    while($row =mysql_fetch_array($query)){

        $queryMeta=mysql_query("SELECT * FROM wp_postmeta WHERE meta_key='_thumbnail_id' AND post_id=$row[ID]") or die(mysql_error());

        $f=mysql_fetch_array($queryMeta);

        $value=$f[meta_value];

        $queryMeta2=mysql_query("SELECT * FROM wp_postmeta WHERE meta_key='_wp_attached_file' AND post_id=$value") or die(mysql_error());

        $f2=mysql_fetch_array($queryMeta2);

        $value=$f2[meta_value];

        $json.='{"id" : "'.$row[ID].'" ,  "date" : "'.$row[post_date].'", "title" :"'.$row[post_title].'", "link" : "'.$row[guid].'" ,  "image" :"https://dartesanos.com/blog/wp-content/uploads/'.$value.'" },';

    }
    $json=substr($json,0,-1);

    echo $json ."]}";