Retrieve wordpress posts with featured image via SQL

1 Views

I’m using this query in a PHP script outside WordPress to retrieve entries with their featured images 

SELECT  ( SELECT guid FROM wp_posts WHERE id = m.meta_value ) AS url 
FROM wp_posts p, wp_postmeta m
WHERE p.post_type =  'post'
AND p.post_status =  'publish'
AND p.id = m.post_id
AND m.meta_key =  '_thumbnail_id'

…and it works fine.

Read More

But this way I get full-size image URL. I need to retrieve ‘medium’ or ‘thumbnail’ sizes of these images.

¿Any way to achieve this?

Post Views: 1

Related posts

Leave a Reply

4 comments

  1. here is the response :

    SELECT TITRE,DESCR,URL, CONCAT(LEFT(IMG, LENGTH(IMG) - LOCATE('.', 
REVERSE(IMG))),'-150x150.',SUBSTRING_INDEX(IMG, '.', -1)) AS IMG FROM (
SELECT    
p.`post_title` AS TITRE, 
(SELECT `meta_value` FROM wp_postmeta WHERE `post_id` = p.`ID` and `meta_key`='_yoast_wpseo_metadesc') AS DESCR,
p.`guid` AS URL,
(SELECT `guid` FROM wp_posts WHERE id = m.meta_value) AS IMG
FROM wp_posts p, wp_postmeta m
WHERE p.post_type =  'post'
AND p.post_status =  'publish'
AND p.id = m.post_id
AND m.meta_key =  '_thumbnail_id') TT

where DESCR is not null

  2. The following query, adapted from the above, solved my particular problem which was simply to grab the last four posts and their featured images. Plus the post_name from which I could construct a pretty URL

    SELECT title, post_name, date, content, CONCAT(LEFT(image, LENGTH(image) - LOCATE('.', REVERSE(image))),'-150x150.',SUBSTRING_INDEX(image, '.', -1)) AS image
FROM (
  SELECT    
  p.post_title AS title, 
  p.post_status AS 'status', 
  p.post_date AS date,
  p.post_content AS content,
  p.post_name AS post_name,
  (SELECT `guid` FROM wp_posts WHERE id = m.meta_value) AS image
  FROM wp_posts p, wp_postmeta m
  WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
  AND p.id = m.post_id
  AND m.meta_key = '_thumbnail_id'
  ORDER BY date DESC
  LIMIT 4
) TT

    Of course from there it’s easy to make an excerpt etc using:

    for($i=0; $i< $num_rows; $i++){  
  $post_content = mysql_result($query_result, $i, "content"); 
  $post_excerpt = substr($post_content, 0, 90); 
  $post_permalink = $post_url . mysql_result($query_result, $i, "post_name");

  echo $post_permalink; //etc

}

  3. You can try this query for thumbnail size , for medium image i am not sure about the right size if you know the dimension then make custom alias as i made below using the SUBSTRING_INDEX to get the extension of file then i have used CONCAT function with the post_name column and the dimensions + extension ,similarly you can do this for medium size , As all upload goes to the upload folder you can analyze the generated thumbs name are original attachment name + -150x150 or other dimensions so from this logic your thumbs get the name with the dimensions, the attachments of post are stored in post_meta with the post id and having key name _wp_attachment_metadata which stores all the information about different sizes of file but in a serialized form so in mysql query you cannot unserialize the data 

    SELECT    
CONCAT(p.`post_name` ,'-150x150.',
SUBSTRING_INDEX(( SELECT `guid` FROM wp_posts WHERE id = m.meta_value ), '.', -1) )    
 AS `thumbnail`,
 (SELECT guid FROM wp_posts WHERE id = m.meta_value ) AS `full`
FROM wp_posts p, wp_postmeta m
WHERE p.post_type =  'post'
AND p.post_status =  'publish'
AND p.id = m.post_id
AND m.meta_key =  '_thumbnail_id'

    This query works for me to get thumbnail of size 150*150 hope it works for you also

  4. SELECT    
    p.ID,
    p.post_title AS title, 
    p.post_name AS post_name,
   (SELECT meta_value from wp_postmeta where post_id = m.meta_value and meta_key='_wp_attachment_metadata') AS meta_value
FROM 
    wp_posts p, 
    wp_postmeta m
WHERE
        p.post_type = 'post'
    AND p.post_status = 'publish'
    AND p.id = m.post_id
    AND m.meta_key = '_thumbnail_id'
ORDER BY 
    p.post_date DESC
LIMIT 5;

    And then use unserialize PHP function with meta_value