CODEWITHSUNDEEP
sqlmysqlwordpress
Jerome Elkins
Jerome Elkins

Reputation: 108

How can I change an URL inside a field in MySQL?

I have a table called "wp-posts" with a field "post-content". This field contains the text for a blog posts. I'd like to change all records to replace an URL for another one.

Imagine that I can have things like:

This is a test and somewhere there's something like <img src="http://oldurl.com/wp-content/somimg.jpg"> and something like <a href="http://oldurl.com/something">a link</a>."

I want it to be

This is a test and somewhere there's something like <img src="http://newurl.com/wp-content/somimg.jpg"> and something like <a href="http://newurl.com/something">a link</a>."

I need to be able to change this for every record in my table without having to open each post in Wordpress and change them by hand. There has to be a way to do this

Upvotes: 9

Views: 25254

Answers (5)

renoirb
renoirb

Reputation: 559

There is a few places to work that out.

  1. Backup your database
  2. Try using WordPress internal utility
  3. Search for matches
  4. Write your own search and replace

Procedure

1. Backup your database

I generally go like so:

mysqldump -u dbuser -p'dbpassword' --complete-insert --default-character-set=utf8 databasename > /home/renoirb/backups/databasename.sql

I even have this in a crontab on my own server. But that's an other topic.

2. Try using WordPress internal utility

WordPress has it's own utility for that matter. Most of the time, it can be done only with it.

Simply add to your theme's functions.php (see wordpress documentation) file the following line:

update_option('siteurl','http://local.workspace/');
update_option('home','http://local.workspace/');

Assuming you are already running the theme that has that particular functions.php file.

Run the site, by refreshing the page.

Then, comment the lines!

You won't need them anymore.

Otherwise they get to update your database configuration at every page load.

If it is not enough, continue...

3. Search for matches

I personally use Adminer on my deployment, it is a lightweight one-file replacement for PHPMyAdmin.

Why I suggest Adminer? It has a nice search all database feature that I find quite handy in our use-case.

It is a search box, labelled: "Search in data tables".

4. Write your own search and replace

UPDATE `wp_comments` SET `comment_author_url` = REPLACE(`comment_author_url`, "http://url", "https://url");
UPDATE `wp_postmeta` SET `meta_value` = REPLACE(`meta_value`, "http://url", "https://url");
UPDATE `wp_options` SET `option_value` = REPLACE(`option_value`, "http://url", "https://url");

Note that in my case, I was migrating ALL my urls from NON HTTPS to HTTPS.

Hope that was helpful

Upvotes: 1

Tony Cecala
Tony Cecala

Reputation: 26

I recently used http://wordpress.org/extend/plugins/search-and-replace/ to update a site from a development server to the public server. I used the plugin to change all URLs of images from their "00.00.00.00/~user/" to their "example.com" format.

It worked perfectly.

Of course, be sure to backup the database first in case you happen to make a typo during the process.

Upvotes: 0

markratledge
markratledge

Reputation: 17561

There's a handy Wordpress plugin that I've used to search and replace in posts pages with grep:

http://urbangiraffe.com/plugins/search-regex/

Upvotes: 1

user76430
user76430

Reputation:

This can be easily achieved with a simple SQL statement using MySQL's replace() function. Before we do that, you should definitely do a database dump or whatever you use for backups. It's not only that it's The Right Thing To Do™, but if you make a mistake on your substitution, it might prove difficult to undo it (yes, you could rollback, but you might only figure out your mistake later on.)

To create a database dump from MySQL, you can run something like this --

mysqldump -h hostname -u username -p databasename > my_sql_dump.sql

Where (and you probably know this, but for the sake of completeness for future generations...) --

  • hostname is a placeholder for the database hostname. If the database server is running on your own machine, then you can either use "localhost" or simply leave the "-h hostname" off entirely
  • username is a placeholder for the user with permission to run a dump on the database. This is often an admin, but if it's a shared db, it might simply be you.
  • databasename is the name of the whole database containing your tables. (Note that the "-p" has nothing to do with this database name. "-p" indicates that MySQL should ask you for a password in order to log in.)

Now that we got that out of the way, you can log in to the MySQL database using:

mysql -h hostname -u username -p databasename

And simply run this statement:

UPDATE `wp-posts` SET `post-content` = REPLACE(`post-content`, "http://oldurl.com", "http://newurl.com");

And that should do it!

If you make a mistake, you can often rerun the statement with the original and new texts inverted (if the new text -- in your case the new URL -- didn't already exist in the text before you did the replace.) Sometimes this is not possible depending on what the new text was (again, not likely in your case.) Anyway, you can always try recovering the sql dump --

cat my_sql_dump.sql | mysql -h hostname -u username -p databasename

And voilà.

Upvotes: 26

Eric
Eric

Reputation: 95123

Use the replace string function in MySQL:

UPDATE MyTable SET textfield = REPLACE(textfield, "http://oldurl.com/", "http://newurl.com")

Upvotes: 5

Related Questions