使用SQL更新自定义邮政类型分类

时间：2019-10-29 作者：Doron Davidowitz

我正在尝试更新帖子类别，但我保留了错误消息。我已经建立了一个更新表单。每个类别都是不同的产品类型（和自定义帖子）。除类别外，所有其他字段均正常工作

我使用this wonderful guide .

我将第一个查询设置为联接表：

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\';
果然我得到了那张桌子。

第二步是更新表

我的代码是

UPDATE wp_term_taxonomy SET term_taxonomy_id = $cat_id WHERE object_id= $post_id;
我得到的只是错误

1054-“where子句”中的“object\\u id”列未知，但我可以看到它。对象id就在那里。

为什么会出现此错误？我做了什么或我不知道我不应该做什么，并且以任何方式做了什么？

1 个回复
最合适的回答,由SO网友:Sally CJ 整理而成

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\'] ))
标签： mysql   小码农  

相关推荐

使用SQL将帖子批量分配到类别(MySQL)

我正在将wordpress站点从QTranslate-X迁移到WPML（2个多语言插件）。在迁移过程中，我所有属于给定类别（英语中为Health，法语中为sain）的帖子都正确地用英语分配，但法语中的链接丢失了。我编写了一个SQL脚本，从属于健康类别的英语帖子中确定法语帖子ID的列表。这给了我一个ID列表（101102…）。然后，我继续运行以下INSERT语句（第一个数字是对象ID，也称为post ID，第二个数字是类别ID，最后一个数字是给定对象的顺序）。这一点描述得很好here:INSERT INTO