Updating WordPress site URLs for different hosts

Why oh why does WordPress insist on storing the site URLs within the database?
Every other project I can think of either keeps it in a config file or doesn’t bother with one and just works with whatever URL it finds itself on.

This is most irritating when you have:

  • more than one developer working on the same project
  • more than one system for hosting the project (dev, test, live, etc)

One way to cope with this irritation is to have a collection of .sql files to handily update the database from the CLI.

For example, a WP site has the URL www.idophp.co.uk on a live host and uses idophp.iain on a dev host.

We would have 2 .sql files such as live_options.sql and dev_options.sql.

In live_options.sql:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://idophp.iain', 'http://www.idophp.co.uk') WHERE option_value LIKE 'http://idophp.iain%';

In dev_options.sql:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://www.idophp.co.uk', 'http://idophp.iain') WHERE option_value LIKE 'http://www.idophp.co.uk%';

These settings can be imported over the top of a database without altering anything but the site URLs using:

$ mysql -u idophp -p idophp < live_options.sql
You can leave a response, or trackback from your own site.

One Response to “Updating WordPress site URLs for different hosts”

  1. [...] One of the most annoying/lazy/strange things about WordPress is that it uses serialised arrays within database fields. This makes a simple search and replace fail if you need to do anything more than update the two fields here. [...]

Leave a Reply