WordPress Get Images using MySQL

1 Views

I have an old function which uses the post excerpt to hold the image thumbnail. It was a bit hackey and worked for a long time.

Now I need to use the post excerpt for, you know, an excerpt. So I’m looking to update this function to grab the image src info straight from the post attachments instead.

Read More

The question:

How can I update the $before_sql SQL code below to grab the first attached image in the post attachment?

The code:

(I think I am only concerned about the sql portion as the rest should clean itself up?) There’s more code section too, but instead of pasting ALL of it here, this snippet should be enough. 

$before_sql = "SELECT ID, post_title, post_excerpt FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post' and post_date < '$cur_post_date' ORDER BY post_date DESC LIMIT $thumbnum";
$before_results = $wpdb->get_results($before_sql);
if($before_results) {
    foreach ($before_results as $before_result) {
        $post_title = stripslashes($before_result->post_title);
        $permalink = get_permalink($before_result->ID);
        $post_excerpt = ($before_result->post_excerpt);
        $output="<div class="thumbnails"><a href="" . $permalink . "" title="Permanent Link: " . $post_title . "">" . $post_excerpt . "</a><br />&lsaquo;</div>n         " . $output;
    }
}
Post Views: 1

Related posts

Leave a Reply

1 comment

  1. To get the first attachment with the posts in one query you can do in this way

    SELECT *,
(SELECT guid FROM `wp_posts`  WHERE post_type ='attachment' AND post_parent=wp.`ID` ORDER BY post_date ASC LIMIT 1 ) AS attachment
 FROM `wp_posts` wp

    ORDER BY post_date ASC will get the first image if you want the latest uploaded image you can simply use DESC ORDER BY post_date DESC

    Here is your query 

    $before_sql = "SELECT ID, post_title, post_excerpt,
(SELECT guid FROM $wpdb->posts  WHERE post_type ='attachment' AND post_parent=wp.`ID`
ORDER BY post_date ASC LIMIT 1 ) AS attachment
FROM $wpdb->posts wp WHERE wp.post_status = 'publish' AND wp.post_type = 'post'
and wp.post_date < '$cur_post_date' ORDER BY wp.post_date DESC LIMIT $thumbnum";

    It works fine for me

    This is the query which will fetch those posts only which has the attachments on it 

    $before_sql = "SELECT ID, post_title, post_excerpt,
(SELECT guid FROM $wpdb->posts  WHERE post_type ='attachment' AND post_parent=wp.`ID`
ORDER BY post_date ASC LIMIT 1 ) AS attachment
FROM $wpdb->posts wp WHERE wp.post_status = 'publish' AND wp.post_type = 'post'
and wp.post_date < '$cur_post_date' HAVING attachment IS NOT NULL ORDER BY wp.post_date DESC LIMIT $thumbnum";