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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.