PHP script to change the site url for structured pages in an ExpressionEngine MSM site

ee_msm_pages_siteurl_fix.php

<?php
/*

This is a fix for wrong URLs when moving content between environments and using Structure with ExpressionEngine MSM sites.
(This is probably not specific to Structure, but I do not use the native Pages add-on).

This is in no way secure. *Do not leave this available on your webserver at any time*.
Be warned: This is a super-hack. There is probably a better way to do this, but this works for me.

I give this file a completely random name, and delete it immediately from my production server when I'm done.

Backup your database, and give it a test run in your development environment first.

*/


die(); // Comment out if you want this to work.

print '<pre>'; // Make the text easier to read

// This needs to be set, or this will fail.
$site_id = $_GET['s'];

// Fill in your your credentials
$db_hostname = '';
$db_username = '';
$db_password = '';
$db_database = '';

$new_url = 'http://www.example.com/'; // This is your new site url. Keep the trailing slash.

$query = "SELECT site_pages FROM exp_sites WHERE site_id = " . $site_id;

$mysqli = new mysqli($db_hostname, $db_username, $db_password, $db_database);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

// I like to print all the things.
if ($result = $mysqli->query($query)) {
    $obj = $result->fetch_object();

    // Print the raw database value
    print '$obj->site_pages' . "\n";
    print $obj->site_pages;
    print "\n\n";

    // Print an array of the decoded value
    $all_pages = unserialize(base64_decode($obj->site_pages));
    print '$all_pages' . "\n";
    var_dump($all_pages);
    print "\n\n";

    // Set and print an array of the new values
    $new_pages = $all_pages; // Copy the thing
    $new_pages[$site_id]['url'] = $new_url; // Here's where the magic happens: Set the new site URL
    print '$new_pages' . "\n";
    var_dump($new_pages);
    print "\n\n";

    $insert_pages = base64_encode(serialize($new_pages));
    print '$insert_pages' . "\n";
    print $insert_pages; // Copy this and insert it as the new value in the appropriate row in the 'site_pages' column in the 'exp_sites' table.
    print "\n\n";

    $result->close();
}

$mysqli->close();

print "And we're done";