CODEWITHSUNDEEP
mysqlsqlwordpresscategories
Adam
Adam

Reputation: 6132

Query on Wordpress database returns invalid categories for a post

I'm trying to get the categories assigned to a post in the WordPress database via the following query.

However, prod_Hats is definitely NOT assigned to the post (checking via the Wordpress UI), however, it IS returned with this query. What I also find strange is that the prod_Hats row has a mismatch in the values for term_taxonomy_id and term_id, since I explicitly JOIN tables based on the equality of those column values.

What is wrong with my query?

select wt.name,wt.slug,wtr.term_taxonomy_id,wtt.term_id,wtt.taxonomy from 
wp_term_relationships wtr
INNER JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id=wtr.term_taxonomy_id AND wtt.taxonomy='category'
INNER JOIN wp_terms wt ON wt.term_id=wtt.term_taxonomy_id
where wtr.object_id=10

RESULT:

name        slug        term_taxonomy_id    term_id     taxonomy
Must read   must-read   4                   4           category
Tips        tips        9                   9           category
english     english     20                  20          category
prod_Hats   prod_hats   73                  72          category

Also, some new categories I add and assign to this post are not returned at all via this query.

Upvotes: 1

Views: 153

Answers (1)

Hobo
Hobo

Reputation: 7611

Looking at a query I have to do something similar (not near a database to confirm), my joins are like this:

  FROM wp_posts p
       LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
       LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
       LEFT JOIN wp_terms t on tt.term_id = t.term_id

The difference I see is on your line

INNER JOIN wp_terms wt ON wt.term_id=wtt.term_taxonomy_id

I use term_id, not term_taxonomy_id

Upvotes: 1

Related Questions