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 …<\/p>\n\n\n\n\n\n\n\n
This code lists tags in use and their joined posts.<\/p>\n\n\n\n
select t.term_id, t.name, r.object_id, p.post_title \nfrom xx_terms t, xx_term_taxonomy tt, xx_term_relationships r , xx_posts p \nwhere t.term_id = tt.term_id\nand tt.term_taxonomy_id = r.term_taxonomy_id\nand r.object_id = p.ID\nand post_type = \"post\"\nand tt.taxonomy = \"post_tag\"\norder by t.term_id asc\n\n\nSpelling mistakes where there is no good tag in the tag list can be corrected as follows<\/code><\/pre>\n\n\n\n#\n# This works for spelling mistakes where there is no correct alternative\n#\nupdate xx_terms t\nset t.name = \"new tag value\"\nwhere t.term_id = \"XXX\" OR t.term_id = \"XXX\"\n;\n<\/pre>\n\n\n\nI used OR not IN because I only had two. (This shouldn’t wotk because it leaves two links where there should only be one.)<\/p>\n\n\n\n
I need some code to discover unused tags and to merge two (or more tags into one that exists).<\/p>\n\n\n\n
The active links are held in xx_term_relationships table. The tag foreign key is held in the term_taxonomy_id so,<\/p>\n\n\n\n
select * from xx_term_relationships \nwhere term_taxonomy_id in ('830','831','832')\n<\/pre>\n\n\n\npulls a view of the active tag links.<\/p>\n\n\n\n
So<\/p>\n\n\n\n
update xx_term_relationships\nset term_taxonomy_id = 'new value'\nwhere term_taxonomy_id in ( 'value 1','value 2')\n<\/pre>\n\n\n\nshould make unwanted links repoint at new values.<\/p>\n\n\n\n
select term_id, name from xx_terms\nwhere term_id not in (\n select term_taxonomy_id\n from xx_term_relationships)<\/pre>\n\n\n\nshould list those tags no longer connected.<\/p>\n\n\n\n
\nI 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.<\/p>\n\n\n\n
I had another look at this in Aug 2024, and wrote this, <\/p>\n\n\n\n
\n- Find the old links<\/li>\n\n\n\n
- Find the new link row key, it may be necessary to make a new link row<\/li>\n\n\n\n
- Associate the article row with the new link<\/li>\n\n\n\n
- delete the old link when empty<\/li>\n<\/ol>\n\n\n\n
The code snips above are useful for solving circumstances where one wants to remove a link, or correct spelling. <\/p>\n\n\n\n
This might also need to be applied to categories too. <\/p>\n\n\n\n