Bulk assign posts to a category using SQL (MySQL)

Question

Bulk assign posts to a category using SQL (MySQL)

David Brossard

2019年5月26日 21:32

I am migrating a wordpress site from QTranslate-X to WPML (2 multi-lingual plugins).

In the migration, all my posts that belonged to a given category (healthy in English, sain in French) were correctly assigned in English but the link was lost in French.

I wrote a SQL script to determine the list of French post IDs from the English posts that belong to the healthy category. That gave me a list of IDs (101, 102...).

I then proceeded to run the following INSERT statements (the first number is the object ID aka the post ID, the second number is the category ID, and the last one is the order for the given object). This is well described here:

INSERT INTO `wp_term_relationships` VALUES('29017',29411,0);
INSERT INTO `wp_term_relationships` VALUES('29023',29411,0);
INSERT INTO `wp_term_relationships` VALUES('29031',29411,0);
INSERT INTO `wp_term_relationships` VALUES('29035',29411,0);

So on so forth... There are 134 rows in total. The number adds up to what the English category contains. So far... so good! The website behaves correctly. Great.

Except for... one little detail. The count of posts in the category table in the backend shows 0 (zero):

Is this something I should worry about? Did I forget to update something else?

Topic plugin-qtranslate plugin-wpml taxonomy categories Wordpress

Category Web

David Brossard · Accepted Answer · 2019年5月26日 21:32

OK, I think I found the answer myself... As stated in the post I linked to:

wp_term_taxonomy - defines the taxonomy - either tag, category, or custom taxonomy

term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment,
term_id bigint(20) unsigned NOT NULL default 0,
taxonomy varchar(32) NOT NULL default '',
description longtext NOT NULL,
parent bigint(20) unsigned NOT NULL default 0,
count bigint(20) NOT NULL default 0,
PRIMARY KEY  (term_taxonomy_id),
UNIQUE KEY term_id_taxonomy (term_id,taxonomy),
KEY taxonomy (taxonomy)

Note

count bigint(20) NOT NULL default 0,

which the OP describes as

count tracks how many objects are associated with the term+taxonomy pair. For example, given a term of the category taxonomy, count tracks how many posts are in that specific category.

So... the # of posts displayed in the UI is not dynamically determined but rather read from this table.

Bulk assign posts to a category using SQL (MySQL)

About