CODEWITHSUNDEEP
phpmysqlsqldatabasewordpress
Avionicom
Avionicom

Reputation: 191

MySQL Query on Wordpress database

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?

Upvotes: 0

Views: 1199

Answers (1)

chris
chris

Reputation: 638

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

Upvotes: 1

Related Questions