WordPress Tags, maintenance
I have some notes on the wordpress schema; I am trying to tidy up my tag list on my blog, there’s a shed load of tags which only have one entry in the database, there are some spelling mistakes and some inconsistency in the use of spaces and plurals. But I might leave those with only one use on the blog/wiki for the search engines. There are 56 which have become orphans. I have documented the image credit at the bottom of the article. Here are my notes …
This code lists tags in use and their joined posts.
select t.term_id, t.name, r.object_id, p.post_title from xx_terms t, xx_term_taxonomy tt, xx_term_relationships r , xx_posts p where t.term_id = tt.term_id and tt.term_taxonomy_id = r.term_taxonomy_id and r.object_id = p.ID and post_type = "post" and tt.taxonomy = "post_tag" order by t.term_id asc
Spelling mistakes where there is no good tag in the tag list can be corrected as follows
# # This works for spelling mistakes where there is no correct alternative # update xx_terms t set t.name = "new tag value" where t.term_id = "XXX" OR t.term_id = "XXX" ;
I used OR not IN because I only had two. (This shouldn’t wotk because it leaves two links where there should only be one.)
I need some code to discover unused tags and to merge two (or more tags into one that exists).
The active links are held in xx_term_relationships table. The tag foreign key is held in the term_taxonomy_id so,
select * from xx_term_relationships where term_taxonomy_id in ('830','831','832')
pulls a view of the active tag links.
update xx_term_relationships set term_taxonomy_id = 'new value' where term_taxonomy_id in ( 'value 1','value 2')
should make unwanted links repoint at new values.
select term_id, name from xx_terms where term_id not in ( select term_taxonomy_id from xx_term_relationships)
should list those tags no longer connected.
I need some code to merge which is directional i.e. each term has an ID and thus we need to merge a bad link into a good one, and delete the bad one.
The featured image is taken from this page, by Imagefix, I have resized and cropped it, and stored and processed it for the usual reasons, of addressability, performance and longevity.