How to get thumbnail from a wordpress database into an external page

Question

I have a wordpress installation. What i'd like to do is call data directly from the wordpress tables in the database and display them on some other non-wordpress pages.

At the moment i'm successfully pulling in the three most recent posts from the wp_posts table. I can't however see in the schema where the post thumbnail (or featured image) are stored.

How can I get either the featured image, or thumbnail? (either will do!)

Thanks in advance!

Answer 1

Its all in you wp_postmeta table

will query the thumbnail ID of a certain post ID (you need to have your post IDs).

SELECT * FROM 'tksql_postmeta' WHERE post_id=1 AND meta_key='_thumbnail_id'

Imagine the returned thumb id is : 600

SELECT * FROM 'tksql_postmeta' WHERE post_id=600 

will give you two rows : meta_key="_wp_attached_file" will give url meta_key="_wp_attachment_metadata" will give meta info like size etc

Hope this helps better

Answer 2

There is a way to pick up thumbnail url with other post data in one select query.

You need to create special function onetime:

DELIMITER $$
CREATE FUNCTION `get_post_thumbnail`(`incoming_post_id` INT)
    RETURNS TINYTEXT
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    set @src:=null;
    set @thumb_id:=null;

    SELECT 
        meta_value into @thumb_id
    FROM 
        wp_postmeta 
    WHERE 
        `post_id` = incoming_post_id 
        AND 
        meta_key='_thumbnail_id';


    SELECT 
        guid into @src
    FROM 
        wp_posts 
    WHERE 
        ID = @thumb_id;


    return @src;

END
$$
DELIMITER ;

Then you can use it this way:

select get_post_thumbnail(wp_posts.ID) as "thumbnail_url" from wp_posts where wp_posts.ID = 1

i hope that this solution will be useful for somebody.

Answer 3

The exact query that worked for me is:

select meta_value FROM wp_postmeta
WHERE meta_key='_wp_attached_file' and post_id =  
  (SELECT meta_value FROM wp_postmeta 
  WHERE post_id=$postId AND 
  meta_key='_thumbnail_id' )  

Where $postId is the id of your post. Note: You need to add the full path to your uploads folder