Remove tags with less 3 chars

[carlo75]

Hi to all,
I'm trying to remove all tags from hotaru_tags table, where string is less than 3 chars, because a pligg import has overpopulated this table... anyone can help with a simple query SQL?

[mabujo]

Interesting little problem, try this :



SELECT * FROM `hotaru_tags` WHERE CHAR_LENGTH(`tags_word`) = 1 OR CHAR_LENGTH(`tags_word`) = 2 OR CHAR_LENGTH(`tags_word`) = 3 ORDER BY CHAR_LENGTH(`tags_word`)


Edit: Just read it again, that gets everything with 1-3 characters, following query will show less than 3 characters



SELECT * FROM `hotaru_tags` WHERE CHAR_LENGTH(`tags_word`) = 1 OR CHAR_LENGTH(`tags_word`) = 2 ORDER BY CHAR_LENGTH(`tags_word`)

[carlo75]

Perfect, it works like a charm!
I see all this trash... select all... delete ;D

[Nick]

Don't forget that those tags will still be in the Posts table under "post_tags". Fixing that will need some PHP:


$sql = "SELECT post_id, post_tags from " . TABLE_POSTS;
$results = $h->db->get_results($h->db->prepare($sql));
if ($results) {
foreach ($results as $row) {
$updated = FALSE;
$tags = explode(',', urldecode($row->post_tags));
foreach ($tags as $key => $value) {
$tags[$key] = trim($value);
if (strlen($tags[$key]) < 3) {
unset($tags[$key]);
$updated = TRUE;
}
}
if ($updated) {
echo "Original tags: " . urldecode($row->post_tags) . "<br />"; // the original tags
$tags = implode(', ', $tags);
$sql = "UPDATE " . TABLE_POSTS . " SET post_tags = %s WHERE post_id = %d";
//$h->db->query($h->db->prepare($sql, urlencode($tags), $row->post_id));
echo $h->db->prepare($sql, urlencode($tags), $row->post_id) . "<br /><br />"; // query to update without short tags
}
}
}Paste that into your theme's index.php template after the opening comment block.
It won't update the database unless you uncomment the $h->db->query line, so you can test it first and see what it's going to do.

[cecilporter]

is it possible to adapt this code into the tags plugin as an option so admins can delete unwanted tags? I'd like to look at a list of tags and select ones to nuke.