15 Nov 2010 PHP script to recreate empty WordPress post slugs

This post describes how I managed to recreate empty WordPress 3.0 permalink post slugs with the post title of the blog posts using a slightly modified version of the script found on another blog post named Bulk update post slugs in a wordpress blog. This script became handy since some of my post slugs was missing permalink content because the content in my WordPress blog was migrated from an old PostNuke blog and was not SEO friendly.

The only change I did to the script was to use the PHP function rawurlencode() to encode the Norwegian letters æøå into a URL friendly URLs and change the formatting of how the changes was presented to the web browser. It is very important that you remember to make a backup of your WordPress database before you run this script. Just in case you need to revert your post slugs changes.

The script

// change this this to strip old slugs if needed:
//update wp_posts set post_name = '' where guid like '%.asp'
set_time_limit(20000);

/** Loads the WordPress Environment and Template, allowing wp functions like the_title() */
define('WP_USE_THEMES', false);
require('./wp-blog-header.php');

function bleach($which)
{
    $result = sanitize_title(get_the_title($which));
    return $result;
}

$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'wordpress';

$sql = 'SELECT ID, post_title' . ' FROM `wp_posts`' . ' WHERE post_status = "publish"' . " and post_name = '' " . ' order by ID asc';

$db = mysql_connect($dbhost, $dbuser, $dbpass) or die('Could not connect: ' . mysql_error());
mysql_select_db($dbname);

$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $id         = $row['ID'];
    $title      = $row['post_title'];
    $clean_slug = rawurlencode(bleach($id));

    echo "ID:{$row['ID']} " . "post_title : {$title} " . "sanitized : {$clean_slug}
";
    $sql_u = "UPDATE `wp_posts` SET post_name = '" . $clean_slug . "' " . 'WHERE ID = ' . $id;
    echo 'QUERY:' . $sql_u . '
';
    mysql_query($sql_u) or die('ERROR: ' . mysql_error());
    flush();
}
echo "";
mysql_close($db);

Save this PHP script in the root path of your WordPress installation. In my case I named it “regenerate_post_slugs.php” and ran it from my web browser by visiting the web address http://example.com/regenerate_post_slugs.php Remeber to add the <?php and ?> PHP tags in the beginning and end of the script.
The changes the script does to your WordPress database will be printed on your web browsers window. If the changes does not meet your post slug wishes, just revert to the backup you made before running this script.

This is an excerp from the result after running the script on my WordPress database

...
ID:1273 post_title : Presentasjon av spillere og kampoversikt klart! sanitized : presentasjon-av-spillere-og-kampoversikt-klart
QUERY:UPDATE `wp_posts` SET post_name = 'presentasjon-av-spillere-og-kampoversikt-klart' WHERE ID = 1273
ID:1274 post_title : Problemer på hjemmesiden! sanitized : problemer-pa-hjemmesiden
QUERY:UPDATE `wp_posts` SET post_name = 'problemer-pa-hjemmesiden' WHERE ID = 1274
...

You can download the script regenerate_post_slugs.php if you experience that some of the content is missing. Remember to rename it to .php

Tags: permalink, PHP, post slugs, Wordpress

Posted by Hans-Henry Jakobsen