Support » Fixing WordPress » Adding tags via SQL
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 nametaxonomy
——–
1023) 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
—–
3Use 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
—–
34) 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 DESCList 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
—–
127) 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
- The topic ‘Adding tags via SQL’ is closed to new replies.
(@planetphillip)
14 years, 8 months ago
Hi,
is there a way for me to add a specific tag based on a specific piece of text in only my comments?
I run a review site and use images in my comments to show readers what I think, along with a text review. There are only three types of images, similar to traffic lights.
I would like to add threee different tags based on those images. The tags would only be added to the post if a very specific phrase is found in my comment.
IS this possible?
TIA