How to change the database prefix in multisite (WPMU) install

1 comment

1. Just call the following function but make sure to change new_prefix with whatever table prefix you want to add and also check whether config file path is correct at your side, i have used find_wp_config_path() function to find it but sometime it may return null.

   function find_wp_config_path() {
       $dir = dirname(__FILE__);
       do {
           if( file_exists($dir."/wp-config.php") ) {
               return $dir;
           }
       } while( $dir = realpath("$dir/..") );
       return null;
   }
   
   function change_table_prefix() {
   
       global $wpdb, $table_prefix;
       $old_table_prefix = $table_prefix;
       $old_prefix_length = strlen( $old_table_prefix );
       $table_new_prefix = 'new_prefix';
   
       /* Config file path */
       $path = find_wp_config_path();
   
       $error = $wpdb->set_prefix( $table_new_prefix );
       if ( is_wp_error( $error ) )
           wp_die( '<strong style="color: #ff0000;">ERROR</strong>: table prefix can only contain numbers, letters, and underscores.' );
   
   
       //Get the table resource
       $result = mysql_list_tables(DB_NAME);
   
       //Count the number of tables
       $num_rows = mysql_num_rows( $result );
       $table_count = 0;
   
       //Rename all the tables name
       for ($i = 0; $i < $num_rows; $i++) {
   
           //Get table name with old prefix
           $table_old_name = mysql_tablename($result, $i);
   
           if ( strpos( $table_old_name, $old_table_prefix ) === 0 ) {
   
               //Get table name with new prefix
               $table_new_name = $table_new_prefix . substr( $table_old_name, $old_prefix_length );
   
               //Write query to rename tables name
               // $sql = "RENAME TABLE $table_old_name TO $table_new_name";
               $sql = "RENAME TABLE %s TO %s";
   
               //Execute the query
               if ( false === $wpdb->query($wpdb->prepare($sql, $table_old_name, $table_new_name)) ) {
                   $error = 1;
                   echo "<p class='error'>", $table_old_name, " table name not updated.</p>";
               } else {
                   //echo "<p class='success'>$table_old_name table name updated to $table_new_name.</p>";
                   $table_count++;
               }
           } else {
               continue;
           }
       }
       if ( @$error == 1 ) {
           echo "<p class='error'>Please change the above tables prefix to ", $table_new_prefix, " manualy.</p>";
       } else {
           echo "<p class='success'>", $table_count, " tables prefix updated successfully.</p>";
       }
   
       //Update the wp-config.php file
       $configFile = file($path);
       foreach ($configFile as $line_num => $line) {
           switch (substr($line,0,16)) {
               case '$table_prefix  =':
                   $configFile[$line_num] = str_replace($old_table_prefix, $table_new_prefix, $line);
                   break;
           }
       }
   
       //making the the config readable to change the prefix
       @chmod($path, 0777);
       if ( is_writeable($path) ) {
           $handle = fopen($path, 'w');
           foreach( $configFile as $line ) {
               fwrite($handle, $line);
           }
           fclose($handle);
   
           echo '<p class="success">wp-config.php file updated successfully.</p>';
       } else {
           echo "<p class='error'>File Not Writeable: Please open wp-config.php file in your favurite editor and search 
                 for variable", $table_prefix, " and assign ", $table_new_prefix, " to the same variable.";
       }
   
       //Create query to update option table
       $update_option_table_query = "UPDATE " . $table_new_prefix . "options 
                                     SET option_name='" . $table_new_prefix . "user_roles' 
                                     WHERE option_name='" . $old_table_prefix . "user_roles' 
                                     LIMIT 1";
   
       //Execute the update query to update option table user_roles row
       if ( false === $wpdb->query($update_option_table_query) ) {
           echo "<p class='error'>Changing value: ",
                $old_table_prefix,
                "user_roles in table ",
                $table_new_prefix,
                "options to  ",
                $table_new_prefix,
                "user_roles</p>";
   
           echo "<p class='error'>End of updating options table data with above error.</p>";
       } else {
           echo "<p class='success'>Updated options table data successfully.</p>";
       }
   
       //Create query to update user_meta table
       $custom_sql = "SELECT user_id, meta_key 
                       FROM " . $table_new_prefix . "usermeta 
                       WHERE meta_key 
                       LIKE '" . $old_table_prefix . "%'";
   
       $meta_keys = $wpdb->get_results( $custom_sql );
   
       //Update all the meta_key field value which having the old table prefix in user_meta table
       foreach ($meta_keys as $meta_key ) {
   
           //Create new meta key
           $new_meta_key = $table_new_prefix . substr( $meta_key->meta_key, $old_prefix_length );
   
           $update_user_meta_sql = "UPDATE " . $table_new_prefix . "usermeta 
                                   SET meta_key='" . $new_meta_key . "' 
                                   WHERE meta_key='" . $meta_key->meta_key . "' 
                                   AND user_id=" . $meta_key->user_id;
   
           $wpdb->query($update_user_meta_sql);
   
       }
       echo "<p class='success'>Updated usermeta table data successfully.</p>";
   
   }
   

   or you can directly use this plugin http://wordpress.org/plugins/change-table-prefix

Comments are closed.