Posted by admin on

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.

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.


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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close