What to do with removed emoticons?

[Kirill N]

After emoticons are removed, every post that used them shows a broken image:

Example: https://community.invisionpower.com/topic/404121-still-no-only-search-in-titles-option/?page=2

I have plenty of examples of this on my site too which I would like to remove. Is there a way?

[wimg]

Yes, with a bunch of sql commands, provided you know the exact code sequence used for the emoticons to be removed, and provided they don't interfere with anything else, as in, they are not used for anything else.
For 3.4.x:

UPDATE `ibf_posts` SET post=REPLACE(post, 'my_emoticon_code', '');

Replace ibf_posts with any table prefix you used, followed by "posts", and replace 'my_emoticon_code' with the exact emoticon code of the to be deleted emoticons, f.e. ':)' for a laughing smiley. Essentially you are replacing the emoticon with an empty string.

IOW, for  this should do the trick, provided your posts file is named "ibf_posts":

UPDATE `ibf_posts` SET post=REPLACE(post, ':)', '');

What you need to do is go into phpMyAdmin, open your database, click on the ibf_posts table, then click on SQL, and in the SQL box type the above command, and click on Go. Repeat this for all emoticons to be removed.

 

For 4.x it becomes:

UPDATE `forum_posts` SET post=REPLACE(post, 'my_emoticon_code', '');

You may have to add a prefix to the table name if you used any. And replacing the icons gets a bit more complex. You'd have to check the forum_posts table to see how they exactly look, f.e:

<img title=":)" alt=":)" src="/monthly_2015_03/smile.png.084974a8f087facf5be357fe8498fd1a.png">

From the looks of it, those may be nigh on impossible to remove, as these would have to removed one by one, unless you use perl or so to remove them from a backup of the database table, and then restore that, but then, they are stored on disk, so should not disappear at all. Do note that in case you do use perl, that you need at least two backups of the posts table, just in case things go awry.

I would recommend making a backup first, before you do this in any case, as it is easily possible to mess things up completely this way. With a database backup in a safe place, before tackling this, at least you can still go back to the version before removing these emoticons.

HTH, kind regards, Wim

Edit: P.S.: The sample you provided is one of emoticons in uploads. It may be that tose can be removed the 3.4.x way, as th eemoticons gfollow a specific format, such as:

https://community.invisionpower.com/uploads/emoticons/shocked.png :o

You may remove those as follows, first method essentially:  

UPDATE `ibf_posts` SET post=REPLACE(post, 'https://community.invisionpower.com/uploads/emoticons/shocked.png :o', '');

This is of course for the site here; for your own site you'd have to provide your own domain name etc. And again, you need to do it for each missing emoticon.

[querschlaeger]

By the way: Invision will not fix it https://community.invisionpower.com/4bugtrack/archived-reports/make-it-possible-to-delete-an-emoticon-r6310/

[chilihead]

As a workaround maybe upload a 1x1 clear.gif to replace the emoticon, and no broken image.

[Kirill N]

16 hours ago, wimg said:

Yes, with a bunch of sql commands, provided you know the exact code sequence used for the emoticons to be removed, and provided they don't interfere with anything else, as in, they are not used for anything else.
For 3.4.x:

UPDATE `ibf_posts` SET post=REPLACE(post, 'my_emoticon_code', '');

Replace ibf_posts with any table prefix you used, followed by "posts", and replace 'my_emoticon_code' with the exact emoticon code of the to be deleted emoticons, f.e. ':)' for a laughing smiley. Essentially you are replacing the emoticon with an empty string.

IOW, for  this should do the trick, provided your posts file is named "ibf_posts":

UPDATE `ibf_posts` SET post=REPLACE(post, ':)', '');

What you need to do is go into phpMyAdmin, open your database, click on the ibf_posts table, then click on SQL, and in the SQL box type the above command, and click on Go. Repeat this for all emoticons to be removed.

 

For 4.x it becomes:

UPDATE `forum_posts` SET post=REPLACE(post, 'my_emoticon_code', '');

You may have to add a prefix to the table name if you used any. And replacing the icons gets a bit more complex. You'd have to check the forum_posts table to see how they exactly look, f.e:

<img title=":)" alt=":)" src="/monthly_2015_03/smile.png.084974a8f087facf5be357fe8498fd1a.png">

From the looks of it, those may be nigh on impossible to remove, as these would have to removed one by one, unless you use perl or so to remove them from a backup of the database table, and then restore that, but then, they are stored on disk, so should not disappear at all. Do note that in case you do use perl, that you need at least two backups of the posts table, just in case things go awry.

I would recommend making a backup first, before you do this in any case, as it is easily possible to mess things up completely this way. With a database backup in a safe place, before tackling this, at least you can still go back to the version before removing these emoticons.

HTH, kind regards, Wim

Edit: P.S.: The sample you provided is one of emoticons in uploads. It may be that tose can be removed the 3.4.x way, as th eemoticons gfollow a specific format, such as:

https://community.invisionpower.com/uploads/emoticons/shocked.png :o

You may remove those as follows, first method essentially:  

UPDATE `ibf_posts` SET post=REPLACE(post, 'https://community.invisionpower.com/uploads/emoticons/shocked.png :o', '');

This is of course for the site here; for your own site you'd have to provide your own domain name etc. And again, you need to do it for each missing emoticon.

Thank you SO much.

I'm going to backup my database and try this.