SQL command to replace words in all tables

[anarchoi]

hi,

i recently changed my host for my website...

i have a forum and a lot of other tables containing my old URL

i'd like to do a one-time SQL command in phpMyAdmin to look in all tables in the DB and replace my old address by my new one

how is this possible?

[btherl]

There's no simple command to do that.

You can do something like this for each column of each table that might be affected:

[code=php:0]UPDATE table
SET column = REPLACE(column, 'http://old_url', 'http://new_url');[/code]

Make sure you make a backup first!  Something like this can easily have unexpected consequences.

[anarchoi]

each column of each table o_O oh my god

is it possible to make a php script to replace words in all column of a table, at least?

[ataria]

is it possible to make a php script to replace words in all column of a table, at least?

if it has an ID yes.
like, if you made a row that is the ID of the thingy.
you can do '
UPDATE table
SET column = REPLACE(column, 'http://old_url', 'http://new_url') WHERE id > {highest one +1};

kinda weird but it should work..

[btherl]

That doesn't make sense to me, ataria.  Wouldn't that update rows with a particular value in the 'id' column?

You could do it by using mysql_list_tables() and mysql_list_fields() to get all the tables and fields, and then doing an update on each combination.  I think it's worth taking the time to do it manually though, as most columns will not be affected.  And it's possible that some columns should not have the url changed, such as posts by users saying "The forum has moved from http://old_site to http://new_site"

[ataria]

alright, let me explain it more..
the database.
ID - Username
1    nygaints1   
2    jetsalltheway
3    nflfanatic

say, you want to change all the username rows to 'nfl_fan'
just do the 'UPDATE `users` SET `username`='nfl_fan' WHERE `id`<'4''

that way it changes every username box where the ID is less than 4, hence, all the usernames will be changed.

[btherl]

I don't think that's what anarchoi wants to do.  He wants to update all columns of all tables.. so for this table:

CREATE TABLE users
(
  username varchar,
  id integer,
  address varchar,
  comment varchar,
);

He wants to update all of the text columns so that 'old_url' is replaced with 'new_url'.  That's not possible using a standard SQL statement unless you list each column specifically.  But I think he wants it to be automated, so the script itself finds which columns of which tables to update.

That's my understanding anyway (which could well be wrong!)

[ataria]

Ohh! I get it know.
... That's not possible.

[btherl]

It is possible!  Not with static SQL statements though.  He'll need to use mysql_list_tables() to get a list of all the tables.  Then mysql_list_fields() on each table to get the columns.  The output of that also gives the column types, which can be used to choose which tables are text.

Then he needs to generate SQL for each of those columns to update it..

It sounds risky to me though.  I would do it manually myself, unless the number of tables was greater than 30 or so.  Even then, most tables are probably not affected and won't require any updating.

[fert]

how about this
[code]
UPDATE TABLE `table` SET `url`='new_url' WHERE `url`='old_url'
[/code]
that should work within tables

[anarchoi]

[quote author=btherl link=topic=115996.msg472454#msg472454 date=1164258946]
I don't think that's what anarchoi wants to do.  He wants to update all columns of all tables.. so for this table:

CREATE TABLE users
(
  username varchar,
  id integer,
  address varchar,
  comment varchar,
);

He wants to update all of the text columns so that 'old_url' is replaced with 'new_url'.  That's not possible using a standard SQL statement unless you list each column specifically.  But I think he wants it to be automated, so the script itself finds which columns of which tables to update.

That's my understanding anyway (which could well be wrong!)
[/quote]

yeah, thats exactly what i wanted to do :)

[quote]UPDATE TABLE `table` SET `url`='new_url' WHERE `url`='old_url'[/quote]
won't that just look for a column named "url" in my table and then look for words in that column?

[just me and php]

Im A Total Newb To All This But I Have Several Databases And Im Always Moving From On Server To The Other And I Just Download My Databese Open It Up With Note Pad And Goto Edit Find Replace And Type In My Old Url To Find And My New Url To Replace With And I Do The Same With Table Renaming.

Maybe This Is To Simple And I Am Not Understanding His Question.
If This Has Nothing To Do With His Question Then Please Ignore This.