6

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?

Thanks!

11

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.

5

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];
}
  • 1
    Don't depend on the guid field to be a URL ... it's meant to be a unique identifier, not a link. – EAMann Jul 6 '12 at 21:14
  • That's a good point! Is there' a field that stores that actual image URL outside of the _wp_attachment_metadata postmeta field? – chrisguitarguy Jul 6 '12 at 21:15
  • That's what we are trying to figure out... I've found the actual image file on the database, but we need a way to call the image inside the sql query. – Martin Hasan Jul 6 '12 at 21:18
  • Take a look at wp_get_attachment_url() and how it builds the URL for attached images based on the ID ... – EAMann Jul 6 '12 at 21:27
4

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 ...

0

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'
0

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 ."]}";
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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