Author: anmari

I knocked myself around trying to figure out why my plugin was not coping with ‘foreign’, multi byte, Greek, Japanese, Chinese characters.  Those damn questions marks ‘????’ – I had it all sorted before in another plugin – what was going on here ?   I had all the multi-byte functions happening.    Then I remembered – the database tables – of course!

Default Mysql

MySQL’s default database charset is usually latin1 and  thecollation is latin1_swedish_ci.  This can vary based on server configuration.  This is usually set before wordpress is installed.

Default wordpress

WordPress usually creates it’s table with charset utf8 and collation utf8_general_ci.    You can overwrite this in the config file.

See http://codex.wordpress.org/Editing_wp-config.php#Database_character_set.

Fix your wordpress database

You can Alter the database after the fact.  This will help the plugins who haven’t figured out that many of us (you?) haven’t set up our databases as well as they could be.

ALTER DATABASE yourdb CHARACTER SET utf8 COLLATE utf8_general_ci;

You may want to fix the individual tables and fields too – phpmyadmin has the tools to edit (or use the rename – it will let you change the collation).

Plugin authors

Make life easy on your self and others when creating custom tables.  Have a look at the wordpress db schema.  Note that there is a global $charset_collate.  Use it!

 

 global $wpdb, $charset_collate;
if (empty($charset_collate))
    $cachecollation = ' DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci ';
else
    $cachecollation = $charset_collate;

if ($wpdb->get_var("show tables like '$table_name'") != $table_name) {
 $sql = "CREATE TABLE " . $table_name . " (
 id bigint NOT NULL AUTO_INCREMENT,
 field1 text NOT NULL,
 field2 text NOT NULL,
 PRIMARY KEY (id) )
 ".$cachecollation. ";";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

dbDelta($sql);

if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
 error_log($table_name.' not created');
 return false;
 }
 else return true;

 

Resources:

• http://codex.wordpress.org/Editing_wp-config.php#Database_character_set
• http://hakre.wordpress.com/2010/12/26/wordpress-database-charset-and-collation-configuration/
• http://dev.mysql.com/doc/refman/5.5/en/charset.html