XF 2.2 proper way to update old ipb emoticons

briansol

Well-known member
I'm fairly certain this is from IPB conversion some 15 years ago... I have many posts that look like this in the db:
Code:
text text text [IMG]style_emoticons/<#EMO_DIR#>/hmm.gif[/IMG]
text text

I was thinking I could do a simple update replace like:

Code:
UPDATE xf_post SET message = REPLACE(message,'[IMG]style_emoticons/<#EMO_DIR#>/','[IMG]styles/default/xenforo/smilies/');

but, looking at a modern post with xf emoticons, the raw bbcode like
Code:
:)
is stored instead of
Code:
styles/default/xenforo/smilies/smile.gif

Looking a step further to make sure it's not just built in ones, i rebadged the ninja icon as
Code:
:ph34r:
from day one and it also shows as
Code:
:ph34r:
in the xf variant now too.

So, i assume the emoticons have all become part of the sprite sheet and show as SVGs instead of direct embedded the path into the xf_post.message field.

What's the proper way to update these posts? is replacing the path good enough as a patch? Or will that set me up for additional future failure when folder structures inevitably change again (xf3?)

Worse, my icon names don't always line up to their short code
eg
Code:
:shrug2:
is actually
Code:
shrug_n.gif
 
Last edited:
Solution
Just enter the actual smilie code as the replacement string.

SQL:
'UPDATE xf_post SET message = REPLACE(message, '[IMG]style_emoticons/<#EMO_DIR#>/hmm.gif[/IMG]', ':)');

Before:
1612805652433.png

After:
1612805768957.png
1612805778710.png

Brogan

XenForo moderator
Staff member
Just enter the actual smilie code as the replacement string.

SQL:
'UPDATE xf_post SET message = REPLACE(message, '[IMG]style_emoticons/<#EMO_DIR#>/hmm.gif[/IMG]', ':)');

Before:
1612805652433.png

After:
1612805768957.png
1612805778710.png
 
Solution

briansol

Well-known member
ok, that's what i was afraid of, and why i made a comment at the end about how they don't line up.
wondering if i can do a regex and find the top 20 instances to correct instead of all of them as it looks to be a manual effort here
 

Brogan

XenForo moderator
Staff member
You can assign additional replacement text strings for smilies as required.

However, you would still need to enter the short code as the replace string in the query, not the actual image path.

If you are familiar with spreadsheets and functions, you could write a very quick function to parse the original URL into a replacement query for each one.

Whichever way you do it, it's going to require some sort of manual effort.
 

briansol

Well-known member
ugh, the more i dig into this the more crap i find...

Code:
<div align="right">[post=419906]Quoted post[/post]</div>

hard coded html in there from quotes too :(
 

briansol

Well-known member
2 days of manual updates and i'm still finding junk to clean up.

Still haven't found a good way to build the regex to match the postid in the middle of the divs and replace it. i can't figure out how to use the normal $1 variables from regex matching in sql. <- rusty at this!
 
Top