labs.dagensskiva.com

I’ve been working on exporting the content of the old dagensbok.com site, which runs ASP and MSSQL, into an installation of WordPress. Using the MSSQL functionality in PHP, the MSSQL server is accessed from within the WordPress installation I’m importing into. The PHP code that performs the import is contained in a plugin.

A few basic notes on MSSQL versus MySQL:

• The MySQL LIMIT 10 clause becomes ‘SELECT TOP 10‘
• ‘SHOW TABLES‘ becomes ‘“SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’”;‘
• Selecting a random row in MSSQL can be done by ‘ORDER BY NEWID()‘

To do the import into WordPress I’m populating the $_POST variable, as if the post was created in the Admin:

<?
$_POST = array();
$_POST['post_title'] = $ret['header'];
$_POST['post_type'] = 'post';
$_POST['content'] = $ret['text'];
$id = wp_write_post();
add_post_meta($id, 'dbc_author', $ret['fname'] . ' ' . $ret['lname']);
...
?>

where $ret is the variable returned from mssql_fetch_assoc(). Similarly, the comments to a post are added using wp_insert_comment($commentdata); - populating the $commentdata array first.

At dagensskiva.com we don’t necessarily want to list artists by the first letter of their name, e.g. we want The White Stripes to be listed under W, not T. The following MySQL ORDER clause will perform such as sort.

SELECT artist FROM bands ORDER BY IF(LEFT(LOWER(artist), 4) = 'the ', LOWER(RIGHT(artist, LENGTH(artist)-4)), LOWER(artist)) ASC

Which will also ignore the case of the first letter after the The when sorting by transposing down to lowercase. I’m forcing to a hex conversion to deal with åäö, not sure how that will work in character sets other than UTF8.

My feeling is that there is a cleaner way to do this - do let me know if there is a prettier way of doing this.

Edit (01/01/2008): As Johan Dahlin pointed out, locale based sorting is not the work of the developer, but the database. Thanks for the help!