Wordpress Get Images using MySQL

Question

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.

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;
    }
}

M Khalid Junaid · Accepted Answer · 2013-08-09 18:39:44Z

4

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";

edited Aug 9, 2013 at 18:39

answered Aug 9, 2013 at 16:00

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pat Over a year ago

Can't seen to get it to work, and a var_dump($before_sql) shows NULL. I think the issue might be post_parent=wp.'ID'. How can I refine that to be the post ID? I have global $id defined. post_parent=wp.'$id'fails too.

M Khalid Junaid Over a year ago

why you var_dump(sql) ?? you need to do $before_results = $wpdb->get_results($before_sql); and post_parent=wp.ID`` is the correlating alias to pass in subquery run my first query in phpmyadmin you can see it working

Pat Over a year ago

Yes, now I see it's working. Except Using ID`` or 'ID' gave me a NULL on the attachment in the foreach.

M Khalid Junaid Over a year ago

yes null for attachment if the post don't has attachments if post has attachments then it will show the url of image or attachment try to upload the images on any post and then see

Pat Over a year ago

Yeah, this is on a photoblog site with 500 images. I can see post_title, and ID, but attachment is null. I can't even get this to work: $beforeIMG = wp_get_attachment_url($before_result->ID); however echo $before_result->ID; works fine. There are images uploaded that it should be showing.

|

Collectives™ on Stack Overflow

Wordpress Get Images using MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related