• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.5 Is there a way to delete unused tags?

Alfa1

Well-known member
#1
I need to delete all content tags that are not used.
Is there a query I can use or an addon that provides this function?
 

Mike

XenForo developer
Staff member
#4
How are you determining their unused? In the control panel? If you click edit on one of them, are they tagged as permanent?
 

Alfa1

Well-known member
#5
I determine unused if the total uses in admincp is 0 and if I click on the tag there are no threads on the tag page.
If I click edit on one of them then these are not marked as permanent.
 

Alfa1

Well-known member
#10
You can place a request and ask a developer to work for you for free. Maybe there is a skilled developer who is willing to do that. But I doubt it.

I think 30 pounds is almost for free. The alternative is to go through all tags by hand and delete them one by one. I have done this many times over the years, sometimes working on it for weeks. So I personally much appreciate the cheap functionality that saves me from spending a mass of work on unused tags. I will be using it several times per year.
 
#11
i tryed with:

DELETE t, tc, trc FROM xf_tag AS t
left JOIN `xf_tag_content` as tc ON t.tag_id=tc.tag_id
left JOIN xf_tag_result_cache AS trc ON t.tag_id=trc.tag_id
WHERE t.use_count < 4

but need to cleanup thread information ...

xf_threads in colum tags its a blob :(
 
Last edited:

Alfa1

Well-known member
#12
I dont think it will work that way. It needs to affect multiple things including the blobs. I hope you took a backup.
 
#13
well, i have done a cleanup tool. if you have deleted tags with mysql query above.

PHP:
$page = intval($_GET['p']);
$pp = 350;
$start = $page*$pp;

        $link = mysqli_connect($host,$user,$pass);
        if($link) { if(mysqli_select_db($link,$datb)){
        mysqli_query($link,"SET NAMES 'utf8'");
        mysqli_query($link,"SET CHARACTER SET 'utf8'");
        }
        }

        
    $q = "SELECT thread_id,CONVERT(tags USING utf8) AS tagx, LENGTH(tags) AS tl FROM xf_thread WHERE LENGTH(tags)>5 LIMIT $start,$pp";
    $r = mysqli_query($link,$q);
    
            while($row = mysqli_fetch_assoc($r))
            {
                $row_arr[] = $row;
            }
    
    
    foreach($row_arr AS $row){
        $t = unserialize($row['tagx']);
        $threadID = $row['thread_id'];
        echo "<pre>threadID: ".$threadID." # </pre>";
        foreach($t AS $k => $v){
            if($f = mysqli_fetch_assoc( mysqli_query($link,"SELECT tag_id FROM xf_tag WHERE tag='".$v['tag']."'") ) ){
                echo " tagid_".$f['tag_id']."; ";               
            }else {
                echo "<font color=red> no_tagid_found: ".$v['tag']."; </font>";
                unset($t[$k]); //remove not existing tag
            }           
        }
        $tx = serialize($t);
        
        if($row['tagx']!=$tx){
        //update
            
            $q = "UPDATE xf_thread SET tags='$tx' WHERE thread_id='$threadID' LIMIT 1";
            if(mysqli_query($link,$q)){ echo "-!!-"; }else{ echo "-EE-"; }
        
        echo "<br />".$tx;
        echo "<br />".$row['tagx'];
        }else{           
            echo "---ident!";
        }
        
        echo "<hr />";
        
                
    }
    
    $page++;
    echo "<a href='?p=$page'>NEXT PAGE</a><meta http-equiv=refresh content='1,?p=$page' />";
 

Xon

Well-known member
#14
There is a much simpler way to purge tags. And one which doesn't risk SQL injection attacks.

After editing out the exit statement, place this in your webroot, (but don't keep it there!) and when run it will delete all tags with less than 10 uses (change as required).
Code:
<?php
exit();

$startTime = microtime(true);

@set_time_limit(0);
ignore_user_abort(true);
$fileDir = dirname(__FILE__);

require($fileDir . '/library/XenForo/Autoloader.php');
XenForo_Autoloader::getInstance()->setupAutoloader($fileDir . '/library');

XenForo_Application::initialize($fileDir . '/library', $fileDir);
XenForo_Application::set('page_start_time', $startTime);

$dependencies = new XenForo_Dependencies_Public();
$dependencies->preLoadData();

$db = XenForo_Application::get('db');

$tags = $db->fetchAll("
select *
from xf_tag
where xf_tag.use_count < 10;
");

print count($tags) . " about to delete in 2 seconds\n";
sleep(2);
print "Now deleting\n";

foreach($tags as $tag)
{
    $dw = XenForo_DataWriter::create('XenForo_DataWriter_Tag');
    $dw->setExistingData($tag);
    $dw->delete();
}

$threads = $db->fetchAll("
    SELECT thread_id
    FROM xf_thread
    WHERE xf_thread.tags <> 'a:0:{}' and xf_thread.tags <> ''
");

/** @var XenForo_Model_Tag $tagModel */
$tagModel = XenForo_Model::create('XenForo_Model_Tag');

foreach ($threads AS $thread)
{
XenForo_Db::beginTransaction($db);
    $tagModel->rebuildTagCache('thread', $thread['thread_id']);
XenForo_Db::commit($db);
}
The version in Tag Essentials is much more complex as it supports and is aware if Tag Essentials features and permanent tags.

This script will permanently delete data, so take a backup of your database before using it.

I can only offer limited support for this.
 
Last edited: