Professional WordPress Design and Development

Content Identification ❘ 333 exploring the WordPress built-in administrative functions to convert popular blog formats. It concludes with an in-depth development of a custom extraction and import script.Migrating Text Documents We define ‘‘text documents’’ as content primarily associated with a word processor for manipulation, and many text documents will end up as pages in your WordPress site. The content isn’t static in the sense that it’s fixed, but it’s not part of the blog narrative. Brute-force is often the simplest approach here. Copy and paste your text, or export a document as HTML and glue that into a WordPress page. Be warned, however, that most word processing applica- tions insert a huge array of embedded HTML tags, local formatting, and other style elements that make the output page render the way it would as seen from the word processor, but not the way you’d want it to within your WordPress theme structure. If you don’t want to strip out all but the most elemental paragraph, table, and link information, consider exporting the file as raw text and then hand-editing it to match the style of your other pages. It’s ugly, but so is removing half of the document in the form of HTML directives. Even something as simple as the MacOS TextEdit application uses custom HTML tags for paragraphs if you ‘‘Save As’’ an HTML document. Save your text, without any formatting. A variation on this theme is merging wiki entries into a blog. Most wikis have their own somewhat arcane syntax, different enough from HTML to make copy and paste time-consuming but not worth automated editing unless you’re moving a large wiki. If the wiki is really a collection of topics, it makes sense to migrate the wiki pages to WordPress posts, creating categories for each topic or set of topics and relying on tags to even more finely identify the content. The upside to migrating out of a wiki is that you gain use of the WordPress metadata functions, and can parse comments and discussion into comment threads rather than endless edits to the wiki document; the downside is that you lose the edit history contained in platforms like MediaWiki. If you’re running a wiki with a MySQL database as the repository, you can use the custom extract and import script discussed later in this chapter to build a migration tool.Built-In WordPress Import Tools For most WordPress users looking to transport content from one home to another, WordPress offers a variety of built-in import facilities. This section covers the basic conversion process and the use of WordPress eXtended RSS (WXR) files for more flexible or powerful data conversion.Blog Conversion WordPress offers basic importers for commonly used blogging platforms. You can find these built-in importers on the Import dashboard in WordPress, and you’ll find the corresponding PHP functions implementing them in the wp-admin/import folder. The blog conversion tools fall into two main migration categories: they read a file exported by your current blogging platform, or they use the source platform’s API set to pull content and re-post it in WordPress. For example, LiveJournal and Blogger are migrated using their APIs, whereas Movable Type, TypePad, and Roller blogs are han- dled by exporting the blog contents from those platforms into a file that is uploaded and executed by WordPress. Another option is the Google Blog Converter application (http://code.google.com/p/google-blog- converters-appengine/). This Python application is intended to be exactly what it claims: a

334 ❘ CHAPTER 14 MIGRATING TO WORDPRESS converter of different blog formats. Essentially the developers are working to implement each import file format for various platforms using the Blogger import format as a reference point. With this tool you can convert a certain export file from one system into an import file for another system, including WordPress. This tool is also online at http://blogger2wordpress.appspot.com/. We’ve used this tool to successfully convert several web sites to WordPress, but your mileage may vary. Using WordPress eXtended RSS Files What if the simple blog-to-blog conversion doesn’t work, or doesn’t capture enough of the metadata, author information, or other content that you want to migrate? In the most basic case what you are going to do is export your existing content into an XML import file in the WordPress eXtended RSS (WXR) format, which is, as it is named, an extension of the RSS format. The process for creating a WXR file depends on the starting point for your source content. Some appli- cations have a built in WXR exporter function which will create this file for you. For example, the WordPress Export dashboard will create this file, but this is only useful when moving content from an existing WordPress site to a new one. If your source content does not have this functionality, you can create the WXR file by hand in a text editor. The easiest way to start your WXR file, if you have to create it by hand is to use the sitemap process. To create a WXR file using a site map, first you need a site map of your source site. Start with a site map created for search engines, or use a whole site link checker like Xenu (http://home.snafu.de/ tilman/xenulink.html — the site is scary, but the tool works) to create one. This site map will list of all the pages that need to be migrated. We do so many migrations from static HTML or other random CMS systems that rather than working a migration plan for each site, we built a special PHP application that spiders a web site and builds the WXR for us. Assuming each page is somewhat consistent, this works very well. Using a combination of PHP, curl, and jQuery, we can feed the page list in from the site map, parse the HTML, and gather the content, then write out in WXR format. Once you have a WXR import file you can then edit it to make any necessary changes to the file prior to importing. You probably will want to read this whole chapter ahead of time so you are aware of the pitfalls that can affect your import and can benefit from some search and replace on the import file rather than hand-editing or fixing each occurrence later. Because this WXR import file is straight up XML you can edit it in your favorite text editor. This allows you to make some bulk changes to URLs, paths, authors, and anything ahead of doing the import. This can save you a lot of time in the long run, but do not go overboard. WordPress is set up to do an import and can automatically create a lot of information for you based on the import. When editing the WXR import file you can see how the import content will play out. The nice thing about this format is that it really is extended RSS, so the format is simple. Unfortunately there is not much documentation about it on WordPress.org (http://codex.wordpress.org/Importing_Content# Importing_from_an_RSS_feed). There is also some example WXR format to model off of in the Google Blog Converter Google Code web site at http://code.google.com/p/google-blog-converters- appengine/source/browse/trunk/samples/wordpress-sample.wxr.

Content Identification ❘ 335This import file is an easy way to move content from your existing site. As discussed later in thischapter, other ways exist to move sites from other content management systems, but we often findit easier to use the WXR import file method.Outside of the WordPress built-in migration functions, WXR files represent the fastest pah to get yourcontent moved into WordPress. In many cases automating only the ‘‘easy’’ parts is sufficient to get yournew site up and running. Assuming your current site has an Export to WXR, a live RSS feed, or someother export mechanism that you can then hand-edit into a WXR format, this can be your simplestapproach.Building a Custom Import Script More advanced than a simple WXR migration is to extract entries from an existing content man- agement system that is database (ideally MySQL) based, and attempt a direct data manipulation to migrate the content. In this case you need to have your old and new database tables in the same MySQL database. Then you can run a set of SQL scripts that will read from the old database, trans- form them appropriately and import the content into the WordPress tables. This method can get tricky because you are juggling several SQL scripts to export, convert, and then import the content; however, it is also the most flexible and powerful approach that operates at the data management level. Listing 14-1 explores an example script to import data directly into the WordPress database. Take a look at the full source before we break it down. LISTING 14-1: MySQL Import Script<?php//set database connection info for database to import$hostname = \"localhost\";$username = \"USERNAME\";$password = \"PASSWORD\";$sourcedb = \"DATABASE\"; // database to import from$sourcetable = \"stories\"; // table that stores posts to import$sourcecomments = \"comment\"; // table that stores comments to import//set database connection info for WordPress database$destdb = \"WORDPRESS-DATABASE\"; // WordPress database$wp_prefix = \"wp_\"; // WordPress table prefix//database connection$db_connect = @mysql_connect($hostname, $username, $password) or die(\"Fatal Error: \".mysql_error());mysql_select_db($sourcedb, $db_connect);$srcresult = mysql_query(\"select * from $sourcetable\", $db_connect) or die(\"Fatal Error: \".mysql_error()); continues

336 ❘ CHAPTER 14 MIGRATING TO WORDPRESS LISTING 14-1 (continued) // used to generate the dashed titles in the URLs function sanitize($title) { $title = strtolower($title); $title = preg_replace(’/&.+?;/’, ‘’, $title); // kill entities $title = preg_replace(’/[ ˆ a-z0-9 _-]/’, ‘’, $title); $title = preg_replace(’/\s+/’, ‘ ‘, $title); $title = str_replace(’ ‘, ‘-’, $title); $title = preg_replace(’|-+|’, ‘-’, $title); $title = trim($title, ‘-’); return $title; } while ($myrow = mysql_fetch_array($srcresult)) { //generate post title $my_title = mysql_escape_string($myrow[’title’]); //generate post content $my_content = mysql_escape_string($myrow[’content’]); //generate post permalink $myname = mysql_escape_string(sanitize($my_title)); //generate SQL to insert data into WordPress $sql = \"INSERT INTO ‘\" . $wp_prefix . \"posts’ ( ‘ID’ , ‘post_author’ , ‘post_date’ , ‘post_date_gmt’ , ‘post_content’ , ‘post_title’ , ‘post_name’ , ‘post_category’ , ‘post_excerpt’ , ‘post_status’ , ‘comment_status’ , ‘ping_status’ , ‘post_password’ , ‘to_ping’ , ‘pinged’ , ‘post_modified’ , ‘post_modified_gmt’ , ‘post_content_filtered’ , ‘post_parent’, ‘post_type’ ) VALUES

Content Identification ❘ 337( ‘$myrow[sid]’, ‘1’, ‘$myrow[time]’, ‘0000-00-00 00:00:00’, ‘$my_content’, ‘$my_title’, ‘$myname’, ‘$myrow[category]’, ‘’, ‘publish’, ‘open’, ‘open’, ‘’, ‘’, ‘’, ‘$myrow[time]’, ‘0000-00-00 00:00:00’, ‘’, ‘0’, ‘post’ );\"; mysql_select_db($destdb, $db_connect); //execute query mysql_query($sql, $db_connect); // load the ID of the post we just inserted $sql = \"select MAX(ID) from \" . $wp_prefix . \"posts\"; $getID = mysql_query($sql, $db_connect); $currentID = mysql_fetch_array($getID); $currentID = $currentID[’MAX(ID)’]; // retreive all associated post comments $mysid = $myrow[\"pn_sid\"]; mysql_select_db($sourcedb, $db_connect); $comments = mysql_query(\"select * from \" .$sourcecomments. \" where pn_sid = $mysid\", $db_connect); //import post comments in WordPress while ($comrow = mysql_fetch_array($comments)) { $myname = mysql_escape_string($comrow[’pn_name’]); $myemail = mysql_escape_string($comrow[’pn_email’]); $myurl = mysql_escape_string($comrow[’pn_url’]); $myIP = mysql_escape_string($comrow[’pn_host_name’]); $mycomment = mysql_escape_string($comrow[’pn_comment’]); $sql = \"INSERT INTO ‘\" . $wp_prefix . \"comments’ ( ‘comment_ID’ , ‘comment_post_ID’ , ‘comment_author’ , ‘comment_author_url’ , continues

338 ❘ CHAPTER 14 MIGRATING TO WORDPRESS LISTING 14-1 (continued) ‘comment_author_IP’ , ‘comment_date’ , ‘comment_date_gmt’ , ‘comment_content’ , ‘comment_karma’ , ‘comment_approved’ , ‘user_id’ ) VALUES ( ‘’, ‘$currentID’, ‘$myname’, ‘$myemail’, ‘$myurl’, ‘$myIP’, ‘$comrow[date]’, ‘0000-00-00 00:00:00’, ‘$mycomment’, ‘0’, ‘1’, ‘0’ );\"; if ($submit) { mysql_select_db($destdb, $db_connect); mysql_query($sql, $db_connect) or die(\"Fatal Error: \".mysql_error()); } } } //Update comment count mysql_select_db($destdb, $db_connect); $tidyresult = mysql_query(\"select * from $wp_prefix\" . \"posts\", $db_connect) or die(\"Fatal Error: \".mysql_error()); while ($myrow = mysql_fetch_array($tidyresult)) { $mypostid=$myrow[’ID’]; $countsql=\"select COUNT(*) from $wp_prefix\" . \"comments\" . \" WHERE ‘comment_post_ID’ = \" . $mypostid; $countresult=mysql_query($countsql) or die(\"Fatal Error: \".mysql_error()); $commentcount=mysql_result($countresult,0,0); $countsql=\"UPDATE ‘\" . $wp_prefix . \"posts’ SET ‘comment_count’ = ‘\" . $commentcount . \"’ WHERE ‘ID’ = \" . $mypostid . \" LIMIT 1\";

Content Identification ❘ 339 $countresult=mysql_query($countsql) or die(\"Fatal Error: \".mysql_error()); } ?>At first glance this looks a little complicated, so let’s break it down and discuss each section of theimport script: //set database connection info for database to import $hostname = \"localhost\"; $username = \"USERNAME\"; $password = \"PASSWORD\"; $sourcedb = \"DATABASE\"; // database to import from $sourcetable = \"stories\"; // table that stores posts to import $sourcecomments = \"comment\"; // table that stores comments to import //set database connection info for WordPress database $destdb = \"WORDPRESS-DATABASE\"; // WordPress database $wp_prefix = \"wp_\"; // WordPress table prefixFirst you set your database connection info. You also set the table names for the source of the contentyou plan on importing. This example assumes both the source tables and WordPress tables exist in thesame database. Then you set your WordPress tables and table prefix.Next you need to initialize your database connections as shown here: //database connection $db_connect = @mysql_connect($hostname, $username, $password) or die(\"Fatal Error: \".mysql_error()); mysql_select_db($sourcedb, $db_connect); $srcresult = mysql_query(\"select * from $sourcetable\", $db_connect) or die(\"Fatal Error: \".mysql_error());After setting your database connections you execute a query to select the data from your sourcetable. This is the data you are going to import into WordPress as posts. Next you create your san-itize function for creating permalinks. This function removes and replaces any characters that arenot legal for URLs and also replaces spaces with dashes to conform to the WordPress permalinkstructure. // used to generate the dashed titles in the URLs function sanitize($title) { $title = strtolower($title); $title = preg_replace(’/&.+?;/’, ‘’, $title); // kill entities $title = preg_replace(’/[ ˆ a-z0-9 _-]/’, ‘’, $title); $title = preg_replace(’/\s+/’, ‘ ‘, $title); $title = str_replace(’ ‘, ‘-’, $title);

340 ❘ CHAPTER 14 MIGRATING TO WORDPRESS $title = preg_replace(’|-+|’, ‘-’, $title); $title = trim($title, ‘-’); return $title; } After your sanitize function is in place you start your while loop to loop through the data you are going to import as posts in WordPress: while ($myrow = mysql_fetch_array($srcresult)) { Next you set variables for your post title, content, and permalink values: //generate post title $my_title = mysql_escape_string($myrow[’title’]); //generate post content $my_content = mysql_escape_string($myrow[’content’]); //generate post permalink $myname = mysql_escape_string(sanitize($my_title)); Notice you send the values through the mysql_escape_string function. This PHP function escapes a string for use in a MySQL query. Next you create the query to insert the post data into the WordPress posts table: //generate SQL to insert data into WordPress $sql = \"INSERT INTO ‘\" . $wp_prefix . \"posts’ ( ‘ID’ , ‘post_author’ , ‘post_date’ , ‘post_date_gmt’ , ‘post_content’ , ‘post_title’ , ‘post_name’ , ‘post_category’ , ‘post_excerpt’ , ‘post_status’ , ‘comment_status’ , ‘ping_status’ , ‘post_password’ , ‘to_ping’ , ‘pinged’ , ‘post_modified’ , ‘post_modified_gmt’ , ‘post_content_filtered’ , ‘post_parent’, ‘post_type’ ) VALUES ( ‘$myrow[sid]’, ‘1’, ‘$myrow[time]’, ‘0000-00-00 00:00:00’,

Content Identification ❘ 341 ‘$my_content’, ‘$my_title’, ‘$myname’, ‘$myrow[category]’, ‘’, ‘publish’, ‘open’, ‘open’, ‘’, ‘’, ‘’, ‘$myrow[time]’, ‘0000-00-00 00:00:00’, ‘’, ‘0’, ‘post’ );\";As you can see, you set specific values for each row in the wp_posts WordPress table. At this pointyou’ll need to match the values you want to import from the source table with the correct table fields inWordPress. The preceding script is just an example showing how that can be accomplished. Next youexecute the generated query: mysql_select_db($destdb, $db_connect); //execute query mysql_query($sql, $db_connect);After your query has successfully run the source data will start to populate in the WordPressposts table. Now you need to import the post comments and associate them with the correctposts. The first step to accomplish this is to load the ID of the post you just inserted, as shownhere: // load the ID of the post we just inserted $sql = \"select MAX(ID) from \" . $wp_prefix . \"posts\"; $getID = mysql_query($sql, $db_connect); $currentID = mysql_fetch_array($getID); $currentID = $currentID[’MAX(ID)’];This is the ID used to associate a comment with a post in WordPress. Next you need to execute a queryto retrieve all of the comments from the source table: // retreive all associated post comments $mysid = $myrow[\"pn_sid\"]; mysql_select_db($sourcedb, $db_connect); $comments = mysql_query(\"select * from \" .$sourcecomments. \" where pn_sid = $mysid\", $db_connect);Next you start a loop to loop through all of the comments attached to this post and insert into theWordPress comments table: //import post comments in WordPress while ($comrow = mysql_fetch_array($comments)) { $myname = mysql_escape_string($comrow[’pn_name’]);

342 ❘ CHAPTER 14 MIGRATING TO WORDPRESS $myemail = mysql_escape_string($comrow[’pn_email’]); $myurl = mysql_escape_string($comrow[’pn_url’]); $myIP = mysql_escape_string($comrow[’pn_host_name’]); $mycomment = mysql_escape_string($comrow[’pn_comment’]); You also set some variables with the comment data you are going to insert into WordPress. Remember these values will need to be matched to whatever system you are importing from. Next it’s time to build the query and insert the comment data in WordPress: $sql = \"INSERT INTO ‘\" . $wp_prefix . \"comments’ ( ‘comment_ID’ , ‘comment_post_ID’ , ‘comment_author’ , ‘comment_author_email’ , ‘comment_author_url’ , ‘comment_author_IP’ , ‘comment_date’ , ‘comment_date_gmt’ , ‘comment_content’ , ‘comment_karma’ , ‘comment_approved’ , ‘user_id’ ) VALUES ( ‘’, ‘$currentID’, ‘$myname’, ‘$myemail’, ‘$myurl’, ‘$myIP’, ‘$comrow[date]’, ‘0000-00-00 00:00:00’, ‘$mycomment’, ‘0’, ‘1’, ‘0’ );\"; if ($submit) { mysql_select_db($destdb, $db_connect); mysql_query($sql, $db_connect) or die(\"Fatal Error: \".mysql_error()); } } } As you can see you match each value to the correct WordPress table field in the INSERT query. After generating your query you initialize the database connection and execute the query. Remember this is in a loop, so if ten comments exist on this post it will execute this INSERT statement for all ten comments.

Content Identification ❘ 343The final section of code in the importer updates the comment_count value on your posts. When viewingtotal comments on a post, WordPress does not dynamically generate that number. Instead it’s stored asan integer value in the post record. The first step is to load a single post to count comments for: //Update comment count mysql_select_db($destdb, $db_connect); $tidyresult = mysql_query(\"select * from $wp_prefix\" . \"posts\", $db_connect) or die(\"Fatal Error: \".mysql_error()); while ($myrow = mysql_fetch_array($tidyresult)) {You also start a while loop to loop through each one of the posts in the WordPress posts table. Nextyou run a SELECT COUNT query to count how many comments this single post has: $mypostid=$myrow[’ID’]; $countsql=\"select COUNT(*) from $wp_prefix\" . \"comments\" . \" WHERE ‘comment_post_ID’ = \" . $mypostid; $countresult=mysql_query($countsql) or die(\"Fatal Error: \".mysql_error()); $commentcount=mysql_result($countresult,0,0);Once this code has executed, the variable $commentcount will contain the total number of commentsattached to this post. The final part is to update the comment_count field in the WordPress posts tableto match this value: $countsql=\"UPDATE ‘\" . $wp_prefix . \"posts’ SET ‘comment_count’ = ‘\" . $commentcount . \"’ WHERE ‘ID’ = \" . $mypostid . \" LIMIT 1\"; $countresult=mysql_query($countsql) or die(\"Fatal Error: \".mysql_error()); }The UPDATE query updates the comment count based on the value of $commentcount. This is a loop so ititerates through each post in the WordPress posts table and updates the comment count for each post.Remember, this is an example of how to create a script to do a direct import from a source databasetable into the WordPress database tables. The individual values set in this script would need to bematched to the appropriate values in your source database tables to import. If you’re up to diggingaround in the WordPress database schema, or have a particularly thorny source data repository towork with, check out the migratedata tool (migratedata.sourceforge.net). migratedata allows youto build a mapping of one database schema to another, creating a SQL file that can be run against yourdestination WordPress installation. The tool stemmed from a project to convert PostNuke-based sitesto WordPress, so the target database schema out of the box is WordPress-centric.Whichever method you choose to transport your content, the next step is to import into WordPress.We recommend importing into a fresh installation of WordPress. Or, make sure that your import planincludes purging existing content so as to avoid duplicate entries. If you are layering your import ontop of existing content you will need to hand-edit your import files or scripts to make sure no conflictsoccur.

344 ❘ CHAPTER 14 MIGRATING TO WORDPRESS Next, do a trial import, and see where you end up. Even with up-front planning and consideration there is very little chance you can get it all right on the first try. Review the new site and see what needs to be hand-edited in the script. Again, there will be more ‘‘grep-fu’’ and find and replace fun. If you are going the WXR route, you can use the WordPress Import dashboard to import this file. An important consideration is the maximum file size and execution time for PHP. If your import file is large, you may need to edit your PHP configuration to increase these timeouts. One way to avoid this problem is using the Advanced Export for WP & WPMU plugin (http://wordpress.org/ extend/plugins/advanced-export-for-wp-wpmu/). This plugin allows you to break up the export into smaller manageable files based on certain criteria like date range, category or author, reducing the risk of WordPress having memory limit issues during import. The export file can be filtered by date range, author, category, content type, post status, or tag and category terms. MEDIA MIGRATION There are really two sets of media and asset files for your site: graphics that make up the theme and site frame, and graphics and documents that are embedded in the content. We discuss the theme presentation later in this chapter. This section is about moving the media that is embedded in your content portions of the page, for example linked Word documents, PowerPoint Presentations and Adobe PDFs. This also includes any images in your content, like screenshots or graphs. In a traditional WordPress installation, these files are uploaded into your uploads folder and, depending on your configuration, are filed by dates. Odds are that your existing site is not going to have this sort of directory structure. But, as you know, link structures and naming conventions matter, and if you are moving from Windows to Linux, case now matters too. You have options for moving your content, and how to structure it in the new site. Each migration is different, so you will have to evaluate which method is going to work out best for your case. Many sites have a top-level folder in the webroot for images, often called /img/ or /images/. The simplest method to move this directory over is just to keep it intact and put it in the top level of your WordPress site. This is primarily copying files from one directory tree on the source server to the new server using a file transfer protocol (FTP) utility and tarball file. Really, we would rather you used a secure file transfer protocol utility (SFTP) to move files, but it all depends on what your server supports. Keeping the original images in the same directory structure on the new server as the old, for example it might be /pdfs/, is beneficial because you may not have to remap each image in the content. However, you will see in the cleaning-up stage that changing the URLs is not that difficult, or you could edit the WXR import file ahead of time. This technique may be undesirable because it breaks from the WordPress convention of storing your assets in the wp-uploads directory and your media assets will become separated into two different locations when you start using the WordPress Write Panels to upload new images. The second option is to move all the images into the WordPress uploads folder on the new server. Make sure the target directory choice matches the WordPress configuration; otherwise you are not really using this method effectively. You can change the default upload directory in WordPress under the Miscellaneous dashboard, as covered in Chapter 3. This option pretty much guarantees that you