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.


<?php

// 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){
    wp_set_password('admin',$admin->ID);
}

// 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") {
 $pageURL .= $_SERVER["SERVER_NAME"].":".$_SERVER["SERVER_PORT"].$_SERVER["REQUEST_URI"];
} else {
 $pageURL .= $_SERVER["SERVER_NAME"].$_SERVER["REQUEST_URI"];
}
$new_siteurl = substr($pageURL,0,strrpos($pageURL,"/"));

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

//And now those pesky hardcoded permalinks

$wpdb->query(
 "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

$wpdb->query(
 "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.

Previous

Bash script to backup all your MySQL databases

Next

Our best product so far

3 Comments

  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 to Michael Cancel reply

Powered by WordPress & Theme by Anders Norén