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

1 Views

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.

Read More

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

Thanks in advance!

Post Views: 1

Related posts

Leave a Reply

3 comments

  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

  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.

  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