How to get a list of all posts and their categories?

admin管理员组

文章数量:1123930

Basically I just need to have a table like this:

Post title	Categories
Post 1	Category 1, Category 2
Post 2	Category 2, Category 2.1
...	...

From MySQL Query to Retrieve Category from wp_posts I successfully workout this query:

SELECT *
FROM wpd9_posts
LEFT JOIN wpd9_term_relationships ON (wpd9_posts.ID = wpd9_term_relationships.object_id)
LEFT JOIN wpd9_term_taxonomy ON (wpd9_term_relationships.term_taxonomy_id = wpd9_term_taxonomy.term_taxonomy_id)
GROUP BY wpd9_posts.ID

But I don't know how to make it better.

Ideally, I prefer to get it from the API, since I don't have SSH access to my hosting. But using SQL and export to a CSV or JSON file is fine.

Basically I just need to have a table like this:

Post title	Categories
Post 1	Category 1, Category 2
Post 2	Category 2, Category 2.1
...	...

From MySQL Query to Retrieve Category from wp_posts I successfully workout this query:

SELECT *
FROM wpd9_posts
LEFT JOIN wpd9_term_relationships ON (wpd9_posts.ID = wpd9_term_relationships.object_id)
LEFT JOIN wpd9_term_taxonomy ON (wpd9_term_relationships.term_taxonomy_id = wpd9_term_taxonomy.term_taxonomy_id)
GROUP BY wpd9_posts.ID

But I don't know how to make it better.

Ideally, I prefer to get it from the API, since I don't have SSH access to my hosting. But using SQL and export to a CSV or JSON file is fine.

• posts
• categories
• database
• sql
• api

Share Improve this question edited Mar 18, 2024 at 12:37 Ooker asked Mar 16, 2024 at 15:07 OokerOoker 33233 silver badges2323 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

If you want to export the data to a CSV or JSON file directly from the SQL query, you can use the MySQL INTO OUTFILE clause.

SELECT wpd9_posts.post_title, GROUP_CONCAT(wpd9_terms.name) AS categories
FROM wpd9_posts
LEFT JOIN wpd9_term_relationships ON (wpd9_posts.ID = wpd9_term_relationships.object_id)
LEFT JOIN wpd9_term_taxonomy ON (wpd9_term_relationships.term_taxonomy_id = wpd9_term_taxonomy.term_taxonomy_id)
LEFT JOIN wpd9_terms ON (wpd9_term_taxonomy.term_id = wpd9_terms.term_id)
WHERE wpd9_posts.post_type = 'post' 
AND wpd9_posts.post_status = 'publish'
GROUP BY wpd9_posts.ID
INTO OUTFILE '/path/to/export/posts_categories.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

本文标签： How to get a list of all posts and their categories