Adding tags via SQL

Use at your own risk, I am not a wordpress developer by any means and came up with this on my own to help myself quickly add tags to posts where I forgot to initially. It would be easier to use perl or php to do this I think.

To add a tag to multiple posts based on if a word is present in a post’s content via a SQL command requires two SQL commands or two steps. The first will insert the required relationships to the wp_term_relationships table and the second will fix the tag count in the wp_term_taxonomy table. I am not sure if this count gets updated each time a tag is added or if wordpress updates it based on some other event.

I have listed a bunch of querys below just to use as sanity scripts.

1) Create the tag you will be using by adding it to a post.

2) Find the term_taxonomy_id in the db by running the follwing SQL. You will use this in the update statement below.

SELECT wtt.term_taxonomy_id
FROM wp_terms wt
JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id
WHERE name = ‘Flying’ — Your tag name

taxonomy
——–
102

3) Current tag count from the wp_term_taxonomy table, this count must be updated after the insert script runs

SELECT count
FROM wp_term_taxonomy
WHERE term_id = (
SELECT term_id
FROM wp_terms
WHERE name = ‘Flying’ )

count
—–
3

Use this to determine what number (count) to add to the update query that fixes the count after the insert runs

SELECT count( * ) AS count
FROM wp_term_taxonomy wtt
JOIN wp_term_relationships wtr ON wtt.term_taxonomy_id = wtr.term_taxonomy_id
WHERE wtt.term_id = (
SELECT term_id
FROM wp_terms
WHERE name = ‘Flying’ )
AND wtt.taxonomy = ‘post_tag’

count
—–
3

4) Run these sanity scripts and save the results for comparison

List all posts that have the word ‘Fly’ contained within the body and show all tags currently associated to each post.

SELECT *
FROM wp_posts wp
JOIN wp_term_relationships wtr ON wp.id = wtr.object_id
JOIN wp_term_taxonomy wtt on wtr.term_taxonomy_id = wtt.term_taxonomy_id
JOIN wp_terms wt ON wtt.term_id = wt.term_id
WHERE (
post_content LIKE ‘% fly %’
OR post_content LIKE ‘% fly%’
)
AND wp.post_type = ‘post’
AND wtt.taxonomy = ‘post_tag’

same as above with only relevant columns

SELECT wp.id, wp.post_title, wt.name
FROM wp_posts wp
JOIN wp_term_relationships wtr ON wp.id = wtr.object_id
JOIN wp_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
JOIN wp_terms wt ON wtt.term_id = wt.term_id
WHERE (
post_content LIKE ‘% fly %’
OR post_content LIKE ‘% fly%’
)
AND wp.post_type = ‘post’
AND wtt.taxonomy = ‘post_tag’
ORDER BY wp.post_date DESC

List all posts that have the word ‘Fly’ or ‘Fly%’ with id and title exposed

SELECT DISTINCT ID, post_title
FROM wp_posts wp
WHERE (
post_content LIKE ‘% fly %’
OR post_content LIKE ‘% fly%’
)
AND wp.post_type = ‘post’

Same as above but only listing post id

SELECT DISTINCT ID
FROM wp_posts wp
WHERE (
post_content LIKE ‘% fly %’
OR post_content LIKE ‘% fly%’
)
AND wp.post_type = ‘post’

5) Run the insert script to insert the relationships (add the keywords to the posts)

INSERT IGNORE INTO wp_term_relationships( object_id, term_taxonomy_id, term_order )
SELECT DISTINCT wp.id AS col1, ‘102’ AS col2, ‘0’ AS col3
FROM wp_posts wp
JOIN wp_term_relationships wtr ON wp.id = wtr.object_id
JOIN wp_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
JOIN wp_terms wt ON wtt.term_id = wt.term_id
WHERE (
post_content LIKE ‘% fly %’
OR post_content LIKE ‘% fly%’
)
AND wp.post_type = ‘post’
AND wtt.taxonomy = ‘post_tag’

6) Get the tag count again

SELECT count( * ) AS count
FROM wp_term_taxonomy wtt
JOIN wp_term_relationships wtr ON wtt.term_taxonomy_id = wtr.term_taxonomy_id
WHERE wtt.term_id = (
SELECT term_id
FROM wp_terms
WHERE name = ‘Flying’ )
AND wtt.taxonomy = ‘post_tag’

count
—–
12

7) Run this update statement to fix the count in the wp_term_taxonomy table

UPDATE wp_term_taxonomy
SET count = ’12’
WHERE term_taxonomy_id = ‘102’

8) Run the sanity script again and check your results