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

Need a quick SQL query or two

Caelum

Well-known member
#1
Heyas

Basically, I want to replace every mention of a phrase in my XF forum posts, profile comments, PCs, and so on to something else. Specifically, I want to change every occurrence of:
"http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/*"
To:
"http://smileys.mydomain.net/*"
With * being a wildcard of whatever smiley image comes after.

For what it's worth: pretty much all occurrences of this are wrapped in tags.

Basically, help me out here, which queries would I need to run on MySQL 5.1.52 to do this? :p
Cheers
 

Mikey

Well-known member
#2
I'd start with:
Code:
SELECT * FROM xf_post WHERE message = 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/'
to see what comes up, make sure it's only what you need, then
Code:
UPDATE xf_post SET message 'http://smileys.mydomain.net/' WHERE mesage = 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/'
untested!

Edit: if = brings up no results, try "LIKE"

so,

Code:
UPDATE xf_post SET message 'http://smileys.mydomain.net/' WHERE mesage LIKE '%http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/%'
 

Jake Bunce

XenForo moderator
Staff member
#4
A string replacement query should work for your purposes:

UPDATE xf_post
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

Backup first.

You will need to run the same query on other tables if you want to affect other things like profile posts and personal conversations:

UPDATE xf_profile_post
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

UPDATE xf_profile_post_comment
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

UPDATE xf_conversation_message
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')