1. 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

Discussion in 'XenForo Questions and Support' started by Caelum, Dec 13, 2010.

  1. Caelum

    Caelum Well-Known Member

    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
     
  2. Mikey

    Mikey Well-Known Member

    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#>/%'
     
    Caelum likes this.
  3. Caelum

    Caelum Well-Known Member

    Thanks for the response :p

    No luck so far; selecting with both = and LIKE turned up empty.
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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/')
     
    Mikey, Nasr and Caelum like this.
  5. Caelum

    Caelum Well-Known Member

    That one did the trick.

    Thanks a bunch. :D
     

Share This Page