admin管理员组

文章数量:1026396

I am extracting data from my Wordpress database to analyse site structure. Works pretty good so far but I would like to include the posts main category in the table to enable further analysis. This is my current query:

SELECT post_name, post_content FROM wp_posts WHERE post_type='post' AND post_status='publish'

How can I enhance this query to include the main category in the result? I am pretty simple when it comes to SQL queries and would love to learn more.

I am extracting data from my Wordpress database to analyse site structure. Works pretty good so far but I would like to include the posts main category in the table to enable further analysis. This is my current query:

SELECT post_name, post_content FROM wp_posts WHERE post_type='post' AND post_status='publish'

How can I enhance this query to include the main category in the result? I am pretty simple when it comes to SQL queries and would love to learn more.

  • database
  • mysql
Share Improve this question asked Apr 1, 2019 at 22:07 Peter PrevosPeter Prevos 1234 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 0

You need to JOIN the term tables with the posts table, something like this:

SELECT
    p.post_name,
    p.post_content,
    t.name
FROM wp_posts p
JOIN wp_term_relationships tr ON ( tr.object_id = p.ID )
JOIN wp_term_taxonomy tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )
JOIN wp_terms t ON ( t.term_id = tt.term_id )
WHERE
    p.post_type='post'
AND
    p.post_status='publish'
AND
    tt.taxonomy = 'category'

Let me know if you have any questions!

I am extracting data from my Wordpress database to analyse site structure. Works pretty good so far but I would like to include the posts main category in the table to enable further analysis. This is my current query:

SELECT post_name, post_content FROM wp_posts WHERE post_type='post' AND post_status='publish'

How can I enhance this query to include the main category in the result? I am pretty simple when it comes to SQL queries and would love to learn more.

I am extracting data from my Wordpress database to analyse site structure. Works pretty good so far but I would like to include the posts main category in the table to enable further analysis. This is my current query:

SELECT post_name, post_content FROM wp_posts WHERE post_type='post' AND post_status='publish'

How can I enhance this query to include the main category in the result? I am pretty simple when it comes to SQL queries and would love to learn more.

  • database
  • mysql
Share Improve this question asked Apr 1, 2019 at 22:07 Peter PrevosPeter Prevos 1234 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 0

You need to JOIN the term tables with the posts table, something like this:

SELECT
    p.post_name,
    p.post_content,
    t.name
FROM wp_posts p
JOIN wp_term_relationships tr ON ( tr.object_id = p.ID )
JOIN wp_term_taxonomy tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )
JOIN wp_terms t ON ( t.term_id = tt.term_id )
WHERE
    p.post_type='post'
AND
    p.post_status='publish'
AND
    tt.taxonomy = 'category'

Let me know if you have any questions!

本文标签： databaseI would like some help wth an SQL query to link posts with categories

版权声明：本文标题：database - I would like some help wth an SQL query to link posts with categories 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745637141a2160519.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。