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.

So

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.

I had another look at this in Aug 2024, and wrote this,

1. Find the old links
2. Find the new link row key, it may be necessary to make a new link row
3. Associate the article row with the new link
4. delete the old link when empty

The code snips above are useful for solving circumstances where one wants to remove a link, or correct spelling.

This might also need to be applied to categories too.

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.