I am looking for a SQL Query that can give the list of all categories created in WordPress Site along with it's category IDs. Please advise a query to get it as category/term relationship is quite complex in WordPress.

I got this but didn't work -

SELECT ID,post_title FROM wp_posts INNER JOIN wp_term_relationships ON wp_term_relationships.object_id=wp_posts.ID INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id=wp_term_relationships.term_taxonomy_id INNER JOIN wp_terms ON wp_terms.term_id=wp_term_taxonomy.term_id WHERE name='category'
  • it's a lot easier to use the API than to write a raw SQL query from scratch, if you have WP CLI there's a subcommand to do it
    – Tom J Nowell
    Aug 24 '20 at 9:51
  • Tom, I don't have installed or able to install WP CLI on the server. The only way i can opt is SQL Query.
    – amitdutt24
    Aug 24 '20 at 10:09
  • 1
    That's not true, there are APIs for doing this. Raw SQL is unnecessary, even if you do not have WP CLI. i guarantee it. Even then, if it's a 1 time event you can download the database, and run WP CLI locally on your computer. Additionally, why do you want this and what format are you trying to retrieve it in? Is this to give to a client? Are you piping the result into a function/class? Do you need to output the full list on a page template? Or are you just trying to get an easy way to find out a categories ID? What's the context behind this
    – Tom J Nowell
    Aug 24 '20 at 11:29

I finally got the solution. Below SQL Query gets the list of all categories with it's ID from WordPress table -

SELECT wp_term_taxonomy.term_id, wp_terms.name FROM wp_term_relationships 
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) 
LEFT JOIN wp_terms ON (wp_terms.term_id = wp_term_taxonomy.term_taxonomy_id) 
WHERE wp_term_taxonomy.taxonomy = 'category' 
GROUP BY wp_term_taxonomy.term_id 
order by wp_terms.name

Hope this could help others.

  • how i can parent_id for category ? Sep 13 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.