SQL add product_tag to WooCommerce database

Manually adding product tags to the WooCommerce database via SQL requires inserting records into multiple interconnected WordPress tables: wp_terms, wp_term_taxonomy, and wp_term_relationships.

Warning: Directly manipulating the database with SQL queries is advanced and can cause data corruption if not done correctly. It is highly recommended to use the WordPress administration interface or the built-in PHP functions (wp_set_object_terms()) for managing product tags.

Prerequisites

You have access to your WordPress database (e.g., via phpMyAdmin or a command line client).
Your table names might have a different prefix than wp_. Adjust the queries accordingly.
You need the ID of the product you want to tag.

SQL Steps to Add a Product Tag

Here are the general steps and SQL queries involved.

1. Insert the new tag into wp_terms (if it doesn’t exist)

This creates the fundamental term record.

sql

INSERT INTO wp_terms (name, slug, term_group) 
VALUES ('New Tag Name', 'new-tag-slug', 0);

2. Insert the taxonomy relationship into wp_term_taxonomy (if it doesn’t exist)

This defines the term as a product_tag taxonomy type.

sql

INSERT INTO wp_term_taxonomy (term_id, taxonomy, description, parent, count) 
VALUES ([TERM_ID], 'product_tag', '', 0, 0);

Note: You need to get the [TERM_ID] from the previous insert. You can retrieve it using SELECT LAST_INSERT_ID(); immediately after the first insert.

3. Relate the tag to the product in wp_term_relationships This links the product (object) to the specific tag (term taxonomy).

sql

INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) 
VALUES ([PRODUCT_ID], [TERM_TAXONOMY_ID], 0);

Note: You need the [PRODUCT_ID] and the [TERM_TAXONOMY_ID] from the previous step (or query wp_term_taxonomy where term_id and taxonomy match your new tag).

4. Update the tag count

After linking, you must update the count in wp_term_taxonomy to reflect the new association.

sql

UPDATE wp_term_taxonomy 
SET count = (SELECT COUNT(*) FROM wp_term_relationships WHERE term_taxonomy_id = [TERM_TAXONOMY_ID]) 
WHERE term_taxonomy_id = [TERM_TAXONOMY_ID];

Alternative: Use PHP/WordPress Functions (Recommended)

Using WordPress’s built-in functions within a custom script or a plugin is much safer as it handles all related database updates automatically.

php

<?php
// Make sure you have loaded WordPress environment if running as a standalone script
require_once( $_SERVER['DOCUMENT_ROOT'] . '/wp-load.php' );
$product_id = 123; // Replace with your product ID
$tags = array('New Tag Name', 'Another Tag'); // Array of tags to add
wp_set_object_terms( $product_id, $tags, 'product_tag', true ); // The 'true' appends the terms
?>

Tổng hợp

SQL add product_tag to WooCommerce database

Bài viết cùng chuyên mục, chủ đề