WordPress.org

Ready to get started?Download WordPress

Forums

Mass delete unpopular Tags (10 posts)

  1. mitch247
    Member
    Posted 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years ago #

    Sorry, I have not seen that before.

Topic Closed

This topic has been closed to new replies.

About this Topic