Search & Replace in Database

Here you can find plenty of help with your general coding needs and projects
If it is about a specific program, use the corresponding forum
DO NOT ask basic questions such as "How do I print a variable", use Google for that

Search & Replace in Database

Postby Cognatus » Tue Jul 18, 2006 8:42 am

I need to search for "Interface\\Icons\\" in the Roster database in the wowroster_items table, row item_texture, and replace with nothing or "/images/icons/". Can anyone let me know the best way to go about this? At the moment my page doesn't display any icons because the above text is in the image url path, and they are located elsewhere.
Cognatus
WR.net Apprentice
WR.net Apprentice
 
Posts: 9
Joined: Mon Jul 17, 2006 5:25 am
Location: Canada

Re: Search & Replace in Database

Postby Gaxme » Tue Jul 18, 2006 7:33 pm

Cognatus wrote:I need to search for "Interface\\Icons" in the Roster database in the wowroster_items table, row item_texture, and replace with nothing or "/images/icons/". Can anyone let me know the best way to go about this? At the moment my page doesn't display any icons because the above text is in the image url path, and they are located elsewhere.


Honestly, you're probably better off just moving the images into the Interface\icons folder. Otherwise, you can probably just write a small php program to do it. I don't know of a way to do direct search and replace from a sql query.

G.
Gaxme
Roster AddOn Dev
Roster AddOn Dev
 
Posts: 74
Joined: Mon Jul 10, 2006 3:54 am

Re: Search & Replace in Database

Postby Cognatus » Tue Jul 18, 2006 8:09 pm

I'm working on integrating this with the guild website, so all images used site wide will need to be in the same directory otherwise I need to upload them twice (meaning huge waste of time and disk space). After doing some digging I finally see where it is getting this from... it is the LUA file that specifies the path, Roster just imports it from that file. I can edit the LUA file... but again that is a pain... I'd rather modify Roster to fix the problem before it imports it in the first place.
Cognatus
WR.net Apprentice
WR.net Apprentice
 
Posts: 9
Joined: Mon Jul 17, 2006 5:25 am
Location: Canada

Re: Search & Replace in Database

Postby Gaxme » Tue Jul 18, 2006 10:34 pm

Well, inquiring minds. I have NO idea if this will work, but from the user notes on http://dev.mysql.com/doc/refman/5.0/en/update.html:

Posted by Alex de Landgraaf on July 22 2004 12:07am

You sometimes run into the problem that you want to replace a substring occuring in a column with a different string, without touching the rest of the string. The solution is surprisingly simple, thanks to MySQL:

UPDATE xoops_bb_posts_text
SET post_text=(
REPLACE (post_text,
'morphix.sourceforge.net',
'www.morphix.org'));

using the string function REPLACE, all items in the post_text column with 'morphix.sourceforge.net' get this substring replaced by 'www.morphix.org'. Ideal when writing a script is just too much effort.


According to that, you need to do (untested -- Backup your DB):

Code: Select all
update wowroster_items SET item_texture=(REPLACE (item_texture,'Interface\\Icons\\','/images/icons/'));
update wowroster_items SET item_texture=(REPLACE(item_texture,'Interface\Icons\','/images/icons/'));


Two lines since not all of them are double slashed.

Good luck!

G.
Gaxme
Roster AddOn Dev
Roster AddOn Dev
 
Posts: 74
Joined: Mon Jul 10, 2006 3:54 am

Search & Replace in Database

Postby Cognatus » Wed Jul 19, 2006 6:19 pm

Thanks for the reply man. :) After some digging yesterday I figured it all out, but couldn't post back because the forum was down. For anyone that is interested to know my solution keep reading!

The "Interface\\Icons\\" is actually added from the CharacterProfiler mod, and is in the LUA file when you import it to Roster. So unless you feel like running an SQL update every time you reload your LUA files, then that solution won't work.

As I mentioned I was working on integrating, and the bank mod I had chosen was Looty. I simply had to modify one line of coding to clip out the unwanted portion on line 230 of index.php for the Looty files. Below is what is looks like after modification:

Code: Select all
        $item['icon']        = substr_replace($item['item_texture'], ''018);
 


From there I was able to set the image folder in the Looty config file as I wanted.
Cognatus
WR.net Apprentice
WR.net Apprentice
 
Posts: 9
Joined: Mon Jul 17, 2006 5:25 am
Location: Canada

Re: Search & Replace in Database

Postby Gaxme » Wed Jul 19, 2006 8:26 pm

Problem solved! Thanks for posting the solution too, that'll be a nice little snippet if anyone wants it.

G.
Gaxme
Roster AddOn Dev
Roster AddOn Dev
 
Posts: 74
Joined: Mon Jul 10, 2006 3:54 am

Search & Replace in Database

Postby zanix » Thu Jul 20, 2006 3:18 am

If you want to post a snipit, clean this post up, format it so others can understand it, and post in "The Code Cache" forum

We had losts of good stuff in there before the hack
Read the Forum Rules, the WiKi, and Search before posting!
WoWRoster v2.1 - SigGen v0.3.3.523 - WoWRosterDF
User avatar
zanix
Admin
Admin
WoWRoster.net Dev Team
WoWRoster.net Dev Team
UA/UU Developer
UA/UU Developer
 
Posts: 5543
Joined: Mon Jul 03, 2006 8:29 am
Location: Idaho Falls, Idaho
Realm: Doomhammer (PvE) - US


Return to General Code Help

Who is online

Users browsing this forum: No registered users and 0 guests

cron