Useful queries for moving Wordpress between subdomains

Background: recently had to move several wordpress installations from a server where they had been set up as site.[domainname] to a server where they were to be the more usual www.[domainname] . This required tracking down a number of references in the database and changing them.
All together now:

UPDATE `wp_options` SET option_value = replace( option_value, '/site.', '/www.' );
UPDATE `wp_posts` SET post_content = replace( post_content, '/site.', '/www.' );
UPDATE `wp_posts` SET guid = replace( guid, '/site.', '/www.' ) where post_type = 'attachment';
UPDATE `wp_postmeta` SET meta_value = replace(meta_value, '/site.', '/www.' );

Individually with explanations:
UPDATE `wp_options` SET option_value = replace( option_value, '/site.', '/www.' );
To change the site_url and also any template settings that use a full-url reference to a resource. Also check for these in template files.

UPDATE `wp_posts` SET post_content = replace( post_content, '/site.', '/www.' );
Fix links to other pages in the site and also any files or images which aren't included as attachments

UPDATE `wp_posts` SET guid = replace( guid, '/site.', '/www.' ) where post_type = 'attachment';
You shouldn't change the guids of posts because it can mess with RSS feeds (may or may not be a problem depending on the site). But attachments representing attached images or files do need to have their paths changed. More information in the Wordpress codex which doesn't consider the case of a subdomain changing.

UPDATE `wp_postmeta` SET meta_value = replace(meta_value, '/site.', '/www.' );
This gets elements (often design elements like a page specific banner or sidebar image) which have been attached to posts/pages as custom fields

~~~
The other thing to be very aware of is that all the search engine links (and any other incoming links) to the existing site will point to site.[domainname]. Make sure to set up a "site" subdomain on the new server and 301 redirect it to www.[domainname]

Mailchimp reconciliation

I spent much of today doing a basic implementation of the Mailchimp API into a client site. Individual actions can almost be copy/pasted from the examples but there is one more complicated wrinkle - the customers can unsubscribe from the newsletter via Mailchimp so periodically the two databases need reconciled. I suspect the proper way to do this is via a webhook for instant results but for today I just put a section in the daily cron. It worked first time which I was pretty pleased with.

[API class included at beginning of file] /** * mailchimp reconciliation * @var string $key * @var string $list * @var object $api */ $key = 'your-api-key'; $list = 'your-list-id'; //"Testing Activate" $api = new MCAPI($key); /** * @link http://apidocs.mailchimp.com/api/rtfm/listmembers.func.php * api listMembers($id, $status='subscribed', $since=NULL, $start=0, $limit=100) * @var array $remote_records */ // 1000 records should be plenty to start with $remote_records = $api->listMembers($list, 'subscribed', NULL, 0, 1000); if($api->errorCode) { echo 'Unable to sync with Mailchimp - error '.$api->errorCode.': '.$api->errorMessage; } else { /** * @var array $members * @var resource $local_records */ $members = array(); foreach($remote_records['data'] as $d) { //we put the data value in the key so we can easily remove it with unset() later $members[ $d['email'] ] = 1; } /* debugging print_r($remote_records); echo "\n"; print_r($members); echo "\n"; // */ $local_records = mysql_query("select user_id, email from users where newsletter = '1'"); /** * unset local newsletter flag for people who have unsubscribed * remove them from remote recordset */ if($local_records && mysql_num_rows($local_records) > 0) { while($l = mysql_fetch_assoc($local_records)) { if(!isset($members[ $l['email'] ])) { // has been removed from mailchimp mysql_query("update users set newsletter = '0' where user_id = '{$l['user_id']}'"); echo $l['email']." removed \n"; } unset($members[$l['email']]); } } /** * if there are any records remaining in remote recordset check for a matching local email and update */ foreach($members as $email => $n) { mysql_query("update users set newsletter = '1' where email = '$email'"); echo "$email added if a matching user exists \n"; } }

Quick UI tip re radio buttons

If you have optional information collected via a set of radio buttons you may consider having none selected by default. But if the record is going to be editable at a later date you're going to need an N/A or similar option, even if you just leave the "value" attribute blank because once the user starts clicking (or tabs) into the options one of them must always be checked.

Co-working space setting up in Christchurch?

BizDojo runs co-working spaces in Wellington and Auckland. I have a couple of tabs open to articles which mention they are looking at setting up shop in Chch.
Creativity flows in right space
Coworking 2.0 gains traction down under

This is relevant because I have no idea what the status of Epicentre and Effusion are post-quake, but both sites were the Red Zone and at least one has been demolished.

Managing multiple monitors in Windows 7

Only recently did I attach a second monitor* to my Windows 7 box, and set about trying to configure it with multiple wallpapers. Unfortunately this doesn't seem to be supported natively :( Luckily an acquaintance pointed me at Display Fusion (disclaimer: affiliate link).

Being an earning and responsible developer I opted to pay for the application after the 30 day trial license expired, although I don't actually use any functions that aren't available in the free version (multiple wallpapers, hotkeys to move windows between monitors). Still one day I might want to customise those hotkeys, or one of the may other functions yet unexplored.

*I admit, mostly so I can watch fullscreen video while still keeping an eye on what is happening online. But it has improved my productivity and reducing the amount of alt-tabbing I do has to be better for my wrists.