Ask your WordPress questions! Pay money and get answers fast! (more info)

$wpdb query with custom fields and post thumb template tags? WordPress


Hello there,

I have a $wpdb recent posts query that I'm trying to get the get_post_meta() and get_the_post_thumbnail() work. I've tried a few different queries, but can't seem to get it. Basically I just a recent posts $wpdb query. There's a few reasons that I'm using $wpdb, but a big one is that I'm trying to get better with SQL queries. So please don't post the any other query methods.

[[LINK href=""]]Here's my code[[/LINK]]

*note when I add the following I get a Not Found output. :
AND $wpdb->posts.post_type = 'custom-post-type'

Thank you for your time!

Answers (2)


John Cotton answers:

You had two conflicting post_type values in there.

I'm assuming that "AND $wpdb->posts.post_type = 'images'" was supposed to be a mime_type test.

If so, I'd write that query like this:

SELECT p.ID, p.post_title, p.post_name
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'attachment'
AND p.post_status = 'inherit'
AND p.post_mime_type LIKE 'image%'
ORDER BY p.post_date DESC

The table aliases (p, pm) make the code easier to read and so are good practice.

Also, use a JOIN rather than limiting with a WHERE statement

Chris comments:

Thanks for the advice.

"images" is actually a custom post type, so I would basically be trying to get the recent posts from the images post type along with the attachments that come with the posts, which would be the post_thumbnail(I think). This also still didn't get the get_post_meta.

Sorry I'm still getting use to SQL, so I'm pretty bad at it. Thank you for your help so far though!


timDesain Nanang answers:

Since you are using get_post_meta function, no need postmeta table inside the query.
try this code:

global $wpdb;
$querystr = "
SELECT * FROM $wpdb->posts
WHERE ( post_type = 'attachment' OR post_type = 'images' )
AND ( post_status = 'inherit' OR post_status = 'publish' )
ORDER BY post_date DESC
$pageposts = $wpdb->get_results($querystr, OBJECT);

if ($pageposts):
global $post;
foreach ($pageposts as $post): setup_postdata($post);
<?php the_title(); ?>
<a href="<?php the_permalink() ?>" rel="bookmark"><img src="<?php echo get_post_meta($post->ID, "thumbnail", true); ?>" alt="<?php the_title(); ?>" /></a>
<?php get_the_post_thumbnail( $post->ID ); ?>
<img src="<?php get_post_meta( $post->ID, 'Video_Thumbnail_URL', true ); ?>" />
endforeach; ?>
else :
?><h2 class="center">Not Found</h2><?php

Chris comments:

Thanks a lot tim,

I got what I needed from your code. I screwed up in a few places outside the query as well. I didn't echo get_post_meta() and I used get_the_post_thumbnail() which when I changes it to the_post_thumbnail() it worked.