Update location of smilies...

[Nick]

Is there any way I can mass-update the location of smiley images on my forum?

When I originally imported them, I entered them as being located at http://mysite.com/x/y/z.

The problem is that I just implemented a rewrite rule for my entire domain to redirect non-WWW visit attempts to the WWW version. This results in the images taking a second to load and makes the page seem jumpy. It tries to fetch the image from http://mysite.com and is redirected to http://www.mysite.com which causes the delay.

Is there any way I can update the paths of all smilies from http://mysite.com/x/y/z to http://www.mysite.com/x/y/z?

[calorie]

Here is a generic MySQL query that will do search/replace. Take backups before running this query.

You need to set the values in lowercase:

UPDATE table_name SET field_name = REPLACE(field_name, 'find this string', 'replace with this string');

Tables where you might want to run the query include:

• post
• postedithistory
• postparsed

Again, take backups before running this query in case it doesn't produce the results you want.

[Nick]

Here is a generic MySQL query that will do search/replace. Take backups before running this query.

You need to set the values in lowercase:

UPDATE table_name SET field_name = REPLACE(field_name, 'find this string', 'replace with this string');

Tables where you might want to run the query include:

• post
• postedithistory
• postparsed

Again, take backups before running this query in case it doesn't produce the results you want.

Which one of those will update the actual saved location of the smilies? I would naturally have to go one-by-one in the AdminCP editing each smiley and updating the location. I'd rather use a query to do it all at once.

[Lats]

Check this thread.

[Nick]

Check this thread.

Thanks.

The thing is that I have my smilies separated into categories and each category has its own directory on my server. So do I just need to change the code in order to reflect each directory, and do that for each one?

For example:

UPDATE
    smilie
SET
    smiliepath = replace(smiliepath,'http://mysite.com/smilies/1','http://www.mysite.com/smilies/1')
WHERE
    smiliepath 
LIKE '%http://mysite.com/smilies/1'

Where 1 is the directory name -- and I change it for each directory, re-running the query each time. Is this correct?

[Lats]

If your directories are /1, /2, /3 etc, then you would only need to run it once and removing the /1 from the LIKE clause.

[Nick]

If your directories are /1, /2, /3 etc, then you would only need to run it once and removing the /1 from the LIKE clause.

Based on the code I posted in post #5, wouldn't that change all of the paths to the /1 directory? How will it know the names of the other directories?

[Lats]

I've changed the code slightly, you just need to change oldsite & newsite.

UPDATE
    smilie
SET
    smiliepath = replace(smiliepath,'http://oldsite.com/smilies/','http://www.newsite.com/smilies/')
WHERE
    smiliepath 
LIKE 'http://oldsite.com/smilies/%'

[Nick]

I've changed the code slightly, you just need to change oldsite & newsite.

UPDATE
    smilie
SET
    smiliepath = replace(smiliepath,'http://oldsite.com/smilies/','http://www.newsite.com/smilies/')
WHERE
    smiliepath 
LIKE 'http://oldsite.com/smilies/%'

Worked a charm. Thanks!