Wp foreign characters, Php, Creating Tables

Question mark graffiti by bilal-kamoon

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;

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

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

Be Sociable, Share!

• 

Related posts:

1. Upgrading to Mysql5 – more strange characters An upgrade of a wordpress site’s Database from Mysql 4 to Mysql 5 gave some surprising results – some strange...
2. Timezones, WordPress, Ical, Php…. What a lot of fun this can be (not!) Your Ical file may have a timezone specified, the event may...
3. Multiple Blogs, Domains with wordpress The configuration features now available in wp-config.php now offer you a great deal of flexibility if you have multiple domains...
4. WordPress Constants How should plugins cope with a relocated wordpress in another folder?...
5. Post meta key, sometimes case sensitive, sometimes not….. get_post_meta, update_post_meta, delete_post_meta and uppercase, lowercase meta keys..... case insensitivities play havoc when they are not consistent....