Daniel Dvorkin

My take on WordPress and related geekery

holding the key

Take ownership of a WordPress database

One of the gigs that used to put food on my table was as WPML‘s support manager. Customers that needed help usually ended up sending me a DB dump so I could implement a local copy to reproduce the problem and test whatever their issue might be.

Tired of doing the repetitive task of updating the site url in wp_options table, changing all the urls in the wp_posts table, and changing the password for the admin user, I came up with this little script that will do that for me.

It needs to be said: Be careful of not uploading this script to a live server.


// Bad, bad things WILL happen if you forget to delete this file when you upload the site to a public server
// Did I say bad already?

// Load WordPress environment
require_once( dirname(__FILE__) . '/wp-load.php' );

// First I want to get access. Changing all administrator passwords to "admin". Go read the first comment of this code!
$wp_user_search = new WP_User_Query( array( 'role' => 'administrator' ) );
$admins = $wp_user_search->get_results();

foreach ($admins as $admin){

// Now lets fix the Site URL and Home URL
// But first let's save the current siteurl, for later use.
$old_siteurl = get_option("siteurl");

$pageURL = 'http';
if (isset($_SERVER["HTTPS"]) && $_SERVER["HTTPS"] == "on") {$pageURL .= "s";}
$pageURL .= "://";
if ($_SERVER["SERVER_PORT"] != "80") {
} else {
$new_siteurl = substr($pageURL,0,strrpos($pageURL,"/"));

update_option("siteurl", $new_siteurl);
update_option("home", $new_siteurl);

//And now those pesky hardcoded permalinks

 "UPDATE $wpdb->posts SET guid = replace(guid, '{$old_siteurl}','{$new_siteurl}'); "

// Last but not least, those hardcoded urls (links, images, etc) in the posts contents

 "UPDATE $wpdb->posts SET post_content = replace(post_content, '{$old_siteurl}','{$new_siteurl}');"


Just drop this file in any WordPress installation and execute it on the browser.


Bash script to backup all your MySQL databases


Our best product so far


  1. sam

    This rocks, thanks! Had my own little search and replace thing but this is convenience!

    • Bahja

      Thanks for the tutorial!I disvecored a bug and worked out a fix. If you use the attachment post URL instead of the media file URL, it fails to get the post_id on subsequent updates.function saveMetaData($post_id, $post) { //make sure we’re saving at the right time. //DOING_AJAX is set when saving a quick edit on the page that displays all posts/pages //Not checking for this will cause our meta data to be overwritten with blank data. if ( empty($_POST) || !wp_verify_nonce($_POST[‘awd_nonce_field’],’awd_nonce_action’) || $post->post_type == revision’ || defined( DOING_AJAX’) ){ return; } global $metaBox; global $wpdb; foreach ($metaBox[‘fields’] as $field) { $value = $_POST[$field[‘id’]]; //Convert URL to Attachment ID. if ($field[‘type’] == media’ && !is_numeric($value) ) { // do we have Attachment post URL if(preg_match( /attachment_id=(\d*)$/’,$value, $matches)) { $value = $matches[1]; } // Or media file URL else { $value = $wpdb->get_var( SELECT ID FROM $wpdb->posts WHERE guid = $value’ AND post_type=’attachment’ ); } } update_post_meta($post_id, $field[‘id’], $value); }//end foreach}//end function saveMetaData

  2. Michael

    Just used this again on another WP site, and I had an error that every time I hit Insert into Post , I’d get an unefdined message back in the box. After some digging, I found that the javascript was capturing the wrong value. CODE: [ url = jQuery(html).attr(‘href’); ] should be [ url = jQuery(html).attr(‘src’); ]. Thanks again for this!

Leave a Reply

Powered by WordPress & Theme by Anders Norén