mysql - 在 wordpress 中显示特定类别的帖子

Question

通过这个查询，我从我的 wordpress 数据库中获得了我需要的帖子。我必须做什么才能从特定类别中获取帖子？

$sql = "select DISTINCT  wp_posts.id, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.guid, wp_posts.post_type, featured_image.guid as post_image, wp_posts.post_modified, wp_users.display_name
from wp_posts 
inner join wp_postmeta on wp_posts.id = wp_postmeta.post_id and wp_postmeta.meta_key = '_thumbnail_id' 
inner join wp_posts as featured_image on featured_image.id = wp_postmeta.meta_value
inner join wp_users on wp_users.id = wp_posts.post_author
where  wp_posts.post_status =  'publish' 
AND DATE_FORMAT(wp_posts.post_date, '%d-%m-%Y')='$mydate'
order by wp_posts.ID desc
limit 20";

标签： mysqlwordpress

Answer 1

您必须与另外三个表进行连接：wp_term_relationships、wp_term_taxonomy 和 wp_terms，并且在 where 子句中，您可以从 terms 表的 name 列中搜索类别名称。已更新 sql 查询

$sql = "select DISTINCT  wp_posts.id, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.guid, wp_posts.post_type, featured_image.guid as post_image, wp_posts.post_modified, wp_users.display_name
from wp_posts 
inner join wp_postmeta on wp_posts.id = wp_postmeta.post_id and wp_postmeta.meta_key = '_thumbnail_id' 
inner join wp_posts as featured_image on featured_image.id = wp_postmeta.meta_value
inner join wp_users on wp_users.id = wp_posts.post_author
INNER JOIN wp_term_relationships rel ON wp_posts.ID = rel.object_id
INNER JOIN wp_term_taxonomy taxonomy ON rel.term_taxonomy_id = taxonomy.term_taxonomy_id
INNER JOIN wp_terms terms ON taxonomy.term_id = terms.term_id
where  wp_posts.post_status =  'publish' 
AND terms.name = 'Category you wist to search for'
AND DATE_FORMAT(wp_posts.post_date, '%d-%m-%Y')='$mydate'
order by wp_posts.ID desc
limit 20";