Querying Wordpress thumbnails

Question

Hi,

I am trying to get the thumbnail meta data for the posts in my wordpress blog together with their category and some other data. The thumbnail data is serialized and I will unserialize it later.

Here is the query I have:

    SELECT wpostmeta.meta_value AS url,
    wposts.post_content, wposts.post_title, wposts.post_status, wposts.post_name,
    cat_terms.name AS category_name, cat_terms.term_id AS category_id,
    ttable.meta_value AS thumb_data
    FROM `wp_postmeta` AS wpostmeta,
    `wp_posts` AS wposts
    INNER JOIN wp_term_relationships AS cat_term_relationships ON (wposts.ID = cat_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON (cat_term_relationships.term_taxonomy_id = cat_term_taxonomy.term_taxonomy_id AND cat_term_taxonomy.taxonomy = 'category')
    INNER JOIN wp_terms AS cat_terms ON (cat_term_taxonomy.term_id = cat_terms.term_id)
    LEFT JOIN `wp_postmeta` AS ttable ON (wposts.ID = ttable.post_id AND ttable.meta_key = '_wp_attachment_metadata')
    WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'url'
    AND wposts.post_type = 'post'

Everything works except for the attachment part. 'thumb_data' is NULL.

Could you please help me fix this?

Answer 1

Can you explain exactly what you are trying to achieve - there is no real need to go direct to the database in the vast number of situations.

For example you can use get_posts with 'post_type'=>'attachment' and 'post_mime_type'=>'image' to get image attachments and then output the metadata, and there are many functions inbuilt to deal with resizing, displaying and cropping the thumbnails, such as get_post_thumbnail() etc.

Answer 2

This query solved my problem: $sql = "SELECT wposts.ID AS ID, thumbposts.ID AS thumbposts_ID, postmeta.meta_value AS url, wposts.post_content, wposts.post_title, wposts.post_status, wposts.post_name, cat_terms.name AS category_name, cat_terms.term_id AS category_id, thumb.meta_value AS thumb_data

    FROM `wp_postmeta` AS postmeta,
    `wp_posts` AS thumbposts,
    `wp_postmeta` AS thumb,
    `wp_posts` AS wposts

    INNER JOIN wp_term_relationships AS cat_term_relationships ON (wposts.ID = cat_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON (cat_term_relationships.term_taxonomy_id = cat_term_taxonomy.term_taxonomy_id AND cat_term_taxonomy.taxonomy = 'category')
    INNER JOIN wp_terms AS cat_terms ON (cat_term_taxonomy.term_id = cat_terms.term_id)

    WHERE wposts.ID = postmeta.post_id
    AND postmeta.meta_key = 'url'

    AND thumbposts.post_parent = wposts.ID
    AND thumbposts.post_type = 'attachment'

    AND thumb.post_id = thumbposts.ID
    AND thumb.meta_key = '_wp_attachment_metadata'

    AND wposts.post_type = 'post'";

if($filter == 1){
    $sql .= " AND cat_terms.term_id = '1'";
}
if($filter == 2){
    $sql .= " AND cat_terms.term_id = '3'";
}

$sql .= " GROUP BY wposts.ID
    ORDER BY wposts.post_date ASC";