Get Wordpress Messages with Image Using SQL

Question

I use this query in a PHP script outside of Wordpress to retrieve records with their attributes

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 great.

But this way I get the full-size image URL. I need to get the “medium” or “thumbnail” sizes of these images.

¿Any way to achieve this?

Answer 1

Here is the answer:

 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 

Answer 2

The following query, adapted from the above, solved my specific problem, which was simply to capture the last four messages and their color images. Plus post_name from which I could build 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, it’s easy to make an excerpt from there, 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 } 

Answer 3

You can try this query for thumbnail size, for the middle image I'm not sure about the correct size if you know the dimension, then create a custom alias as I did below using SUBSTRING_INDEX to get the file extension, then I used CONCAT with the post_name column and size + extension, in the same way you can do this for medium size. Since the entire download goes to the download folder, you can analyze the generated thumb original attachment name + -150x150 or other dimensions , so from this logic you will get a name with dimensions, message attachments are stored in post_meta with the message identifier and have the key name _wp_attachment_metadata Strong>, which stores all the information about different file sizes, but in serialized form, so in mysql query you cannot de-aerialize 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 a thumbnail of size 150 * 150, hope this works for you too.