Time for a new plugin

This blog has nearly a thousand posts, and apparently 800+ pieces of related-post data. I find these “see also” links really helpful so I didn’t want to lose them, but I’d been using an outdated related posts plugin for years (the Microkids Related Posts one) and it serves insecure scripts … just urgh. So I decided it was high time to find something else. The key requirements for me are that it’s pretty lightweight, and that it does a good job of searching content when I want to relate a link to a current post (because I have enough content that it’s pretty unwieldy to find things without decent search). I went with “Related” after a whole 3 minutes of research and it seems fine. It also actually documents where it stores its data which was nice.

Migrating the data

The previous plugin stored each related article separately in a table called wp_post_relationships whereas this new plugin has a PHP-serialized array of IDs in the wp_postmeta table so I knew I’d have to fuss around with some data formatting to get to where I needed. To achieve this I went with:

• loop over all the related link data ordered by post ID, look at the ID of the post each one is for and add the related post to a list
• if the next row has a new postID, we’ve got all the data for the previous post, so write it to the new data format and clear the list
• when you get to the end, don’t forget to save the data for the post that was last!

Health Warnings

First: Do not try this at home. Or rather, do try it at home rather than in the cloud or wherever your wordpress is hosted!

I run a development copy on my local machine and deploy theme changes with source control. This means I can restore a database to that version, and screw up to my heart’s content when I’m messing around with these things.

Before you do anything on live, take a backup and check that it worked.

The PHP Code

I wrote this in PHP because I needed more looping that I could easily get from a simple SQL script, and the target format was a PHP-serialized array. Other approaches are probably also valid. The script uses a file config.php for its variables because if I don’t do this, I commit credentials to source control (or, you know, paste them into my blog). Hopefully it gives you some idea of what I did and if you’re looking to migrate between plugins, you may even be able to use this script!

<?php

require("config.php");  // sets $db containing user, pass, dbname

$pdo = new PDO("mysql:host=localhost;dbname=" . $db&#91;'dbname'&#93;, $db&#91;'user'&#93;, $db&#91;'pass'&#93;);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// put this in the global namespace to use and re-use later
$update_stmt = $pdo->prepare("INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (:id, :key, :value)");

// let's start by working through the existing data
$sql = "SELECT post1_id, post2_id FROM wp_post_relationships ORDER BY post1_id ASC";
$stmt = $pdo->query($sql);

$i = 0;
$previous_post_id = 0;
$related_posts = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $current_post_id = $row['post1_id'];
    // is this the same post as before?  If not, do stuff because we've got everything for the previous one
    if($current_post_id <> $previous_post_id && $previous_post_id > 0) {
        // echo "Finished with post " . $previous_post_id . "\n";
        // print_r($related_posts);
        store_posts($previous_post_id, $related_posts);

        // empty the list and start again
        $related_posts = [];
    }

    // echo "POST " . $current_post_id . " has related post " . $row['post2_id'] . "\n";
    $related_posts[] = $row['post2_id'];

    $previous_post_id = $current_post_id;
    $i++;
}
// also do the last one!
store_posts($previous_post_id, $related_posts);

echo "DONE.  $i related posts processed";

// a function since we need to call it within the loop and once after
function store_posts($post_id, $related_posts) {
    global $pdo, $update_stmt; // it's a one-off script.  Sue me.

    $update_stmt->execute([":id" => $post_id, ":key" => "related_posts", ":value" => serialize($related_posts)]);
}

I’m happy with the changes so far, and I’m pleased I took the time to migrate my data instead of losing all those links. The pattern here of having to group together data that starts as separate rows is quite a common one and I almost always take this approach of checking on the start of each loop and then processing the previous data if appropriate.