Transferring your WordPress website from one URL to another, necessitates updating the database entries which reference the old site domain. If you developed your website using a temporary URL then a change will be required when the website is published.
For a WordPress website the standard entries in the database, to be amended, are given below. It assumes the standard WordPress table prefix of wp_. Other tables may also need to be updated, dependant upon the plugins which have been installed.
The WordPress database can be edited directly using PHPMyAdmin, the SQL for this is given below, or if you prefer, use the code as a file.
Change Site URL references in options table
UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldurl.co.uk’, ‘http://www.newurl.co.uk’)
Change GUID references
UPDATE wp_posts SET guid = REPLACE (guid, ‘http://www.oldurl.co.uk’, ‘http://www.newurl.co.uk’)
Change Post/Content references
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘http://www.oldurl.co.uk’, ‘http://www.newurl.co.uk’)
Change Image Path
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘src=”http://www.oldurl.co.uk’, ‘src=”http://www.newurl.co.uk’)
Change image Attachments GUID
UPDATE wp_posts SET guid = REPLACE (guid, ‘http://www.oldurl.co.uk’, ‘http://www.newurl.co.uk’) WHERE post_type = ‘attachment’
Change Post meta data
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, ‘http://www.oldurl.co.uk’,’http://www.newurl.co.uk’)
Alternatively the above can be done automatically using the code below, placed in the root of the website. don’t forget to remove it afterwards, to prevent unwanted access.
<?php
/**
* changesiteurl.php
*
* Author: Neil
* vntweb.co.uk
*
* Change WordPress database URL
*
* This file replaces the old website URL with the new value
* in the options table and other original WordPress tables.
*
* The database connections are imported from the WordPress configuration file.
*
* Upload the file to the root of the WordPress website and then visit /changesiteurl.php
*
*//* include configuration settings */
include_once("wp-config.php");
echo '<style>';
echo 'tr:nth-child(odd) { background-color:#eee; }';
echo 'tr:nth-child(even) { background-color:#fff; }';
echo '</style>';
// echo '<p>';
// echo 'DB_NAME: ' . DB_NAME . '<br />';
// echo 'DB_USER: ' . DB_USER . '<br />';
// echo 'DB_PASSWORD: ' . DB_PASSWORD . '<br />';
// echo 'DB_HOST: ' . DB_HOST . '<br />';
// echo '</p>';
/* show text boxes for old and new URLs */// Options Table
$sOldURL = $_POST['tbxOldURL'];
$sNewURL = $_POST['tbxNewURL'];
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (!$dbc) {
die ("MySQL Connection error");
}
/* Options table */
$q = 'UPDATE wp_options SET option_value = replace(option_value, \'' . $sOldURL . '\', \'' . $sNewURL . '\')';
$r = mysqli_query($dbc, $q);
/* GUID references */
$q = 'UPDATE wp_posts SET guid = REPLACE (guid, \'' . $sOldURL . '\', \'' . $sNewURL . '\')';
$r = mysqli_query($dbc, $q);
/* Post/content references */
$q = 'UPDATE wp_posts SET post_content = REPLACE (post_content, \'' . $sOldURL . '\', \'' . $sNewURL . '\')';
$r = mysqli_query($dbc, $q);
/* Image path */
$q = 'UPDATE wp_posts SET post_content = REPLACE (post_content, \'src="' . $sOldURL . '\', \'src="' . $sNewURL . '\')';
$r = mysqli_query($dbc, $q);
/* Image attachments GUID */
$q = 'UPDATE wp_posts SET guid = REPLACE (guid, \'' . $sOldURL . '\', \'' . $sNewURL . '\') WHERE post_type = \'attachment\'';
$r = mysqli_query($dbc, $q);
/* Post meta data */
$q = 'UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, \'' . $sOldURL . '\',\'' . $sNewURL . '\')';
$r = mysqli_query($dbc, $q);
/* Option to show data from options table */
//$q = 'SELECT * FROM ' . $table_prefix . 'options WHERE option_value like \'%' . $sNewURL . '%\'';
$q = 'SELECT * FROM ' . $table_prefix . 'posts WHERE option_value like \'%' . $sNewURL . '%\'';
$r = mysqli_query($dbc, $q);
if ($r) {
echo '<table>';
while ($row = mysqli_fetch_array($r,MYSQLI_ASSOC)) {
//echo '<tr><td>' . $row['option_name'] . '</td><td>' . $row['option_value'] . '</td></tr>';
echo '<tr><td>' . $row['guid'] . '</td></tr>';
}
echo '</table>';
}
?>
<p>Enter old and new urls in the text boxes below and then click on submit to make the changes to the database.</p>
<form method="post" action="changesiteurl.php">
<div>
<label >Old URL</label>
<input type="text" id="tbxOldURL" name="tbxOldURL" value="<?php echo $sOldURL; ?>" />
</div>
<div>
<label >New URL</label>
<input type="text" id="tbxNewURL" name="tbxNewURL" value="<?php echo $sNewURL; ?>" />
</div>
<div>
<input type="Submit" value="Submit">
</div>
</form>
If the WordPress website was in a sub-directory and is moving into the root of the website the .htaccess file will also need to be changed. In the two examples below the first is where the site was in the sub-directory testsite and the second at the root level.
.htaccess file WordPress in sub-directory testsite
#SetEnv DEFAULT_PHP_VERSION 53#DirectoryIndex index.cgi index.php# BEGIN WordPress
#<IfModule mod_rewrite.c>
#RewriteEngine On
#RewriteBase /testsite/
#RewriteRule ^index\.php$ - [L]
#RewriteCond %{REQUEST_FILENAME} !-f
#RewriteCond %{REQUEST_FILENAME} !-d
#RewriteRule . /testsite/index.php [L]
#</IfModule># END WordPress
.htaccess file WordPress at root level
SetEnv DEFAULT_PHP_VERSION 53
DirectoryIndex index.cgi index.php# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule># END WordPress
Thoughts
With testing I found that changing the URL was not enough.
WordPress saves many of the URL references in arrays with the item reference taking the style of that shown below
‘a:18:{s:23:header_background_image”;s:63:”http://www.example.com/wp-content/uploads/2017/09/823423485.jpg”;}’
As can be seen there is a count of the number of characters.
With the count of characters, the s: value each to be recalculated with each of the replacements.
