WordPress.org

1. mitch247
   Member
   Posted 1 year ago #

   I have 1000's of tags and I would like to simply delete all tags that aren't used more than X times... i.e. 5 times.

   Does anyone know of a simple way to do this? Even straight SQL would totally ROCK!

   Thanks a lot ;)
   Mitch

2. vtxyzzy
   Member
   Posted 1 year ago #

   First, BACK UP YOUR DATABASE!!

   Since you cannot delete from a table and also use that table in a subquery, you must create a new table having the term_taxonomy_id's that need to be deleted:

   [Code Removed as it'll wipe your categories! See later posts.]

   Now, you can use temprel in the subquery of a DELETE:

   DELETE FROM wp_term_relationships
   WHERE term_taxonomy_id IN
     (SELECT term_taxonomy_id FROM temprel)

3. mitch247
   Member
   Posted 1 year ago #

   Thanks a lot for your response! What do you think of this code, will it work?

   $x = 5; // set this to any number
   $sql = "SELECT <code>name</code> FROM <code>wp_terms</code>";
   $result = mysql_query($sql);
   $count = array();
   while($row = mysql_fetch_assoc($result))
   {
     $count[$name]++;
   }
   foreach($count as $key = $value)
   {
     if($value < $x)
       {
         $sql2 = "DELETE FROM <code>wp_terms</code> WHERE <code>name</code> = '". $key ."'";
         $result2 = mysql_query($sql2);
       }
   }

4. vtxyzzy
   Member
   Posted 1 year ago #

   MY CODE IS INCORRECT! DO NOT USE IT!! IT WILL DELETE CATEGORIES AND LINKS AS WELL AS TERMS!!

   Your code is only deleting the wp_terms entries. If you delete from that table, you will leave 'orphan' records. Also, if there happens to be a category with the same name as a tag, it will be deleted. Finally, each tag occurs in the terms table only once, so the count will always be 1.

   The code I gave deletes the wp_term_relationships and leaves no orphans.

   Actually, you need to delete from 3 tables:

   1. wp_term_relationships
   2. wp_term_taxonomy
   3. wp_terms

   If you delete the term_relationships, the tags will no longer show up on any posts. Deleting from the other tables is a little more complex than from term_relationships. I ignored those tables because once the relationships are gone, the tags are 'inactive' so to speak.

5. mitch247
   Member
   Posted 1 year ago #

   So I should do this:

   CREATE TABLE temprel SELECT term_taxonomy_id, count(1) as postcount
   FROM <code>bmc_term_relationships</code>
   GROUP BY term_taxonomy_id
   HAVING count(1) < 5

   and then:

   DELETE FROM wp_term_relationships
   WHERE term_taxonomy_id IN
     (SELECT term_taxonomy_id FROM temprel)

   DELETE FROM wp_term_taxonomy
   WHERE term_taxonomy_id IN
     (SELECT term_taxonomy_id FROM temprel)

   DELETE FROM wp_terms
   WHERE term_taxonomy_id IN
     (SELECT term_taxonomy_id FROM temprel)

6. vtxyzzy
   Member
   Posted 1 year ago #

   DO NOT USE THE FIRST SQL CREATE TABLE STATEMENT I POSTED!!! IT WILL DELETE CATEGORIES AND LINKS AS WELL AS TAGS!!

   Use this for creating the temprel table:

   CREATE TABLE temprel
   SELECT tr.term_taxonomy_id, count(1) AS postcount
   FROM wp_term_relationships tr
   JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
   WHERE tt.taxonomy = 'post_tag'
   GROUP BY tr.term_taxonomy_id
   HAVING postcount < 5

   You could use the sql statements I gave in a wp get-results() call.

7. mitch247
   Member
   Posted 1 year ago #

   vtxyzzy... Thanks a lot for that updated code... I ran the following and still show thousands of tags in my dashboard.

   CREATE TABLE temprel
   SELECT tr.term_taxonomy_id, count(1) AS postcount
   FROM wp_term_relationships tr
   JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
   WHERE tt.taxonomy = 'post_tag'
   GROUP BY tr.term_taxonomy_id
   HAVING postcount < 5

   and

   DELETE FROM wp_term_relationships
   WHERE term_taxonomy_id IN
     (SELECT term_taxonomy_id FROM temprel)

   It seems that that did work at the post level. However I still see thousands in admin. How can I use that new temporary table to clear out the tags in admin as well?
   Thanks again for your help!

8. vtxyzzy
   Member
   Posted 1 year ago #

   As I said before, tags are defined in 3 tables. You have cleared the first one - there are two left: wp_term_taxonomy and wp_terms.

   You can delete the rows in wp_term_taxonomy with this:

   DELETE FROM wp_term_taxonomy
   WHERE term_taxonomy_id IN
     (SELECT term_taxonomy_id FROM temprel)

   Then, delete from wp_terms with this:

   DELETE FROM wp_terms
   WHERE term_id NOT IN
      (SELECT term_id FROM wp_term_taxonomy)

9. mitch247
   Member
   Posted 1 year ago #

   OK I did run it successfully... I moved to my next domain and tried to do the same thing. However I get this error when I try to create that temp table:

   import.php: Missing parameter: import_type (FAQ 2.8)
   import.php: Missing parameter: format (FAQ 2.8)

   Have you ever seen this?

10. vtxyzzy
    Member
    Posted 1 year ago #

    Sorry, I have not seen that before.

Topic Closed

This topic has been closed to new replies.