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

3 回答

• 2

  这是回复：

  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
  

  回复于 2024-05-08T13:21:54+08:00
• 2

  以下查询改编自上面的问题，解决了我的特殊问题，即只 grab 最后四个帖子及其特色图片 . 加上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
  

  当然，从那里开始使用以下内容很容易：

  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
  
  }
  

  回复于 2024-05-08T13:21:54+08:00
• 1

  您可以尝试此查询缩略图大小，对于中等图像我不知道正确的大小，如果你知道维度，然后使用SUBSTRING_INDEX下面使用SUBSTRING_INDEX制作自定义别名，然后我使用CONCAT函数与 post_name 列和维度扩展，类似地你可以为中等大小做这个，因为所有上传到上传文件夹你可以分析生成的拇指名称是 original attachment name + -150x150 or other dimensions 所以从这个逻辑你的拇指得到的名称与维度，帖子的附件存储在post_meta中的帖子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'
  

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

  回复于 2024-05-08T13:21:54+08:00