Update Custom Post Type Taxonomies with SQL

Question

I'm trying to update post category but i keep error messages. I have built an update form. each category is a different product type (and a custom post). all the other fields are working except the category

I use this wonderful guide .

I set the first query to join the table:

SELECT *
FROM wp_term_relationships AS tr
LEFT JOIN wp_posts AS p ON tr.object_id = p.ID
LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type = 'ye_product' AND tt.taxonomy = 'category';

and sure enough i get the table.

step two was to update the table

and my code is

UPDATE wp_term_taxonomy SET term_taxonomy_id = $cat_id WHERE object_id= $post_id;

All i get is the error

1054 - Unknown column 'object_id' in 'where clause'

but I can see it. the object id is right there.

Why am I getting this error? What I did or what I didn't know I shouldn't do and did it any way?

Answer 1

Why am I getting this error?

Because the table wp_term_taxonomy does not have the column object_id.

And the correct table that you should update is the wp_term_relationships table which does have the column object_id. See the WordPress database diagram and also the WordPress database description for more details.

So your SQL query should be:

UPDATE wp_term_relationships SET term_taxonomy_id = $cat_id WHERE object_id = $post_id;

And it could work, but then:

First, the query would affect all rows where the object_id is the value of the $post_id. E.g. When $cat_id = 1 and $post_id = 2:

|------------------------------|------------------------------|
|         Before Update        |         After Update         |
|------------------|-----------|------------------|-----------|
| term_taxonomy_id | object_id | term_taxonomy_id | object_id |
|------------------|-----------|------------------|-----------|
| 10               | 2         | 1                | 2         |
| 11               | 2         | 1                | 2         |
| 35               | 2         | 1                | 2         |

Secondly, you would also need to update the count column in the wp_term_taxonomy table for rows where term_taxonomy_id is in the above list (10, 11, 35 and 1) — e.g. count - 1 for 10, 11 and 35 (i.e. the old categories).

A Better Solution

Instead of messing with custom SQL, you should just use the WordPress term/category APIs like wp_set_post_categories() or wp_set_post_terms() which you can use to easily update the post categories — the later function should be used with custom taxonomies. So:

wp_set_post_categories( $post_id, $cat_id );       // Set $cat_id as the only category
wp_set_post_categories( $post_id, $cat_id, true ); // Or add $cat_id to the existing list

Additional Notes

• You really should escape dynamic data being used in SQL queries; however, I'm just going to assume you've already sanitized both the $cat_id and $post_id values? (e.g. $cat_id = absint( $_POST['cat_id'] ))