Query on WordPress database returns invalid categories for a post

0 Views

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.

Read More

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.

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. 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