fixing missing post slugs in wordpress

August 31st, 2006

If you've ever moved from one house to another, you know that it's not just moving day that is a mess in your new house, it drags on for a while until you get things sorted out. Lots of little details escape attention for days, weeks even. But eventually you track down every last one and after about a month or two, you are 100% in order.

Now you're probably thinking what the hell does that have to do with the title of this entry?!? Well, just like moving houses, migrating data from one system to the next is similar. And moving from BLOG:CMS to WordPress has not been entirely trivial, so I still spot the odd bug even though it's been a couple of weeks. One thing I neglected to consider when migrating the blog was missing post slugs. You see, WordPress uses post slugs as a way to label urls more human-friendly. Instead of {blog_url}?p=34 to open post number 34, it allows you to use urls in the form {blog_url}/index.php/year/month/day/blog-entry-title (the part after the last slash is what WordPress calls a post slug) This is nice for people who link to a blog entry, because the latter url makes a lot more sense to a human than the former (which is just a number of a column in a database).

But. BLOG:CMS does not use post slugs (or didn't), so I've never had them. WordPress generates them automatically for new posts, but since I imported my old entries into WordPress, those didn't have post slugs from before. I realized all this when I migrated my blog entries, and I thought it was just inconsistent, but it wouldn't have any repercussions. Well, it turns out some links were broken over this. So I realized today that I would have to fix this annoying little bug and put in post slugs for entries that don't already have them.

And for that purpose I wrote a little script. It's a quick and dirty fix, stripping off all non-ascii characters (this will not work well with non-English post titles), forcing all characters to lowercase and inserting hyphens between words. But for my money it works well enough.


$dbhost = '';
$dbuser = '';
$dbpass = '';
$dbname = '';

$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());

$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'];
	$title = trim($title);
	$title = strtolower($title);
	$title = str_replace(" ", "-", $title);	
	$title = ereg_replace("[^a-z0-9-]", "", $title);
	$title = ereg_replace("[-]+", "-", $title);
	echo "ID :{$row['ID']} <br>" .
		"post_title : {$row['post_title']} <br>" .
		"post_title : {$title} <br>";

	$sql_u = 'UPDATE `wp_posts` SET post_name = `' . $title .'`'
		.'WHERE ID = ' . $id;
	echo '<br>'.$sql_u;
	mysql_query($sql_u) or die('Query failed: ' . mysql_error());




:: random entries in this category ::

4 Responses to "fixing missing post slugs in wordpress"

  1. venkat says:

    Hi, I discovered this post after a long labourious search for clues to migrate my blogcms site away to Wordpress. Could you pl. tell me what scripts you used or possibly write a step by step instruction (I am sure there will be more people benefiting from this as I see none are available). Thanks in advance.

  2. numerodix says:

    I documented the process of converting from BLOG:CMS to WordPress in some detail in this blog entry:

  3. BlogCMS to Wordpress Migration...

    The Background
    I write this with the hope that someone else will find it useful. This describes about how I migrated my old blog, running on BlogCMS4.0.0j to Wordpress 2.1. This wasn’t as easy as I imagined. In a sense I am a veteran of mig...

  4. [...] till matusiak som delat med sig av sitt skript som den här funktionen baserar sig på. Tack också till [...]