mysql为自定义post类型选择自定义字段(post\u meta)

qkf9rpyu 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(471)

我正在查询wordpress的帖子。
我已经创建了一个自定义的职位类型 People . 每个 people post具有自定义字段 name , age , location , birthday 还有更多。自定义字段是使用高级自定义字段插件创建的。
我想查询所有的自定义字段 People 自定义帖子类型。
我想要这样的输出：

+----+-------------+-----------+--------+-----------------------------+----------+-----+
| id | post_title  | name          | age    | location                    | birthday   | 
+----+-------------+-----------+--------+-----------------------------+----------+-----+
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
|  1 | SAMPLE      | some_name     | XX     | sample_location             | 10/07/1980 |
+----+-------------+-----------+--------+-----------------------------+----------+-----+

正确的语法是什么？
我试过：

SELECT * 
FROM  `wp_posts` ,  `wp_postmeta` 
WHERE  `post_type` =  'people'

但是这个列表列出了所有wordpress常规的post字段。
有人能帮忙吗？

mysql php wordpress custom-post-type advanced-custom-fields

来源：https://stackoverflow.com/questions/54056789/mysql-select-custom-fields-post-meta-for-custom-post-type

2条答案

按热度按时间

pdkcd3nj1#

首先，您从两个表中进行选择，而不连接或设置它们之间的关系（将每个数据集连接到另一个数据集应该是一种关系）
你可以这样做：

SELECT t1.*, t2.* 
FROM posts as t1 
INNER JOIN postmeta as t2 
ON t1.ID = t2.post_id 
WHERE t1.post_type = 'people' 
AND t2.meta_key = 'name'

**注意：您应该注意表的名称，并根据需要在查询中替换表中的列名。

赞(0）回复(0）举报 2021-06-16

px9o7tmv2#

这里是解决任何人可能有同样的问题！

SELECT posts_people.ID AS people_ID,
   posts_people.post_title AS people_post_title,
   (select meta_value from wp_postmeta where meta_key = 'name' AND post_id = posts_people.ID) as name,
   (select meta_value from wp_postmeta where meta_key = 'age' AND post_id = posts_people.ID) as age,
   (select post_title from wp_posts where ID = SUBSTRING_INDEX(SUBSTRING_INDEX((select meta_value from wp_postmeta where meta_key = 'location' AND post_id = posts_people.ID),'";',1),':"',-1)) as location,
   (select meta_value from wp_postmeta where meta_key = 'birthday' AND post_id = posts_people.ID) as stelexos_kinito
FROM wp_posts AS posts_people
WHERE post_type = 'people' and post_status = 'publish'

赞(0）回复(0）举报 2021-06-16

我来回答

mysql为自定义post类型选择自定义字段(post\u meta)

2条答案

相关问题

热门标签

最新问答