通过SQL检索带有特色图像的wordpress帖子

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

我在Wordpress外部的PHP脚本中使用此查询来检索具有其特色图像的条目

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.

......它工作正常。

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?

¿任何方法来实现这一目标?

3 个解决方案

#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

以下查询改编自上面的问题,解决了我的特殊问题,只是抓住了最后四个帖子及其特色图片。加上post_name,我可以从中构建一个漂亮的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

您可以尝试此查询缩略图大小,对于中等图像我不知道正确的大小如果您知道维度然后使用SUBSTRING_INDEX制作自定义别名使用SUBSTRING_INDEX获取文件的扩展名然后我使用了CONCAT函数与post_name列和尺寸+扩展名,类似地你可以为中等大小做这个,因为所有上传到上传文件夹你可以分析生成的拇指名称是原始附件名称+ -150x150或其他尺寸所以从这个逻辑你的拇指得到带有维度的名称,post的附件存储在post_meta中,并带有post id并且具有键名称_wp_attachment_metadata,它存储了有关不同文件大小的所有信息但是以序列化形式存在,所以在mysql查询中你无法反序列化数据

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

此查询适用于我获取大小为150 * 150的缩略图,希望它也适合您

#1