MySQL Query on Wordpress database

Question

I'de like to have a mysql query on a wordpress database that recover the title and the first image link of the last 6 posts.

Wordpress core functions are not permitted because I'd like to show them on an external site. In other words I need the pure mysql query.

I'm able to show the titles in this way:

$result = mysql_query("select * FROM wp_posts WHERE post_status='publish' AND post_type='ad_listing' ORDER BY id desc limit 6" ,$db);
while ($records = mysql_fetch_assoc($result)) {
  echo '<li>'.$records['post_title'] ."</li>";
}

But how to recover the first image (if exists) attached to these posts?

the images links are in the wordpress database (post_type=attachement, guid=url_to_the_image), the files are located in the upload folder of the wordpress site — Avionicom
– Avionicom, Commented Mar 20, 2013 at 21:19

chris · Accepted Answer · 2013-03-20 22:18:57Z

1

For your image records in the wp_posts table, does the post_parent point back at the published page? Mine does not, and if yours doesn't either, then you need to search through the post_content field for each published page, looking for img tags (or whatever tag you use for your images).

From other articles I've read, it appears that sometimes post_parent for an image points back at the parent page. If this is true for your database, then you should be able to do something like this:

SELECT 
    post.id AS post_id, 
    post.post_title, 
    post.guid AS post_url, 
    image_detail.id AS image_id, 
    image_detail.post_title AS image_title, 
    image_detail.guid AS image_url
FROM wp_posts AS post
LEFT JOIN (
    SELECT post_parent, MIN( id ) AS first_image_id
    FROM wp_posts
    WHERE post_type = 'attachment'
        AND post_mime_type LIKE 'image/%'
    GROUP BY post_parent
    ) AS image_latest 
    ON post.id = image_latest.post_parent
LEFT JOIN wp_posts AS image_detail 
    ON image_detail.id = image_latest.first_image_id
WHERE post.post_status = 'publish';

edited Mar 20, 2013 at 22:18

answered Mar 20, 2013 at 22:06

chris

6488 silver badges17 bronze badges

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

2 Comments

Avionicom Over a year ago

Thank you for your answer but it don't work for me. I confirm that in my wordpress db post_parent point back at the published page

Avionicom Over a year ago

After some adaptations to my case, it now works. Thank you very much.

Collectives™ on Stack Overflow

MySQL Query on Wordpress database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related