0

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?

0

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'] ))
| improve this answer | |
  • 1
    Thank you. Of course everything is sanitized – Doron Davidowitz Nov 5 '19 at 7:34
  • That was just a gentle reminder.. So would you mind accepting the answer? :) – Sally CJ Nov 5 '19 at 7:53
  • "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." – Doron Davidowitz Nov 5 '19 at 8:02
  • That's an upvote, I was referring to the checkmark next to my answer which you can tick to mark the answer as "correct" - more details here. – Sally CJ Nov 5 '19 at 9:02

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.