Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm following this to make my plugin auto-create a table when the plugin is activated, but what happens is that while all the tables (the whole db) are utf8_general_ci, the newly created table is latin1_swedish_ci... Why isn't it also utf8? I thought it would also be utf8 by default since I have:

define('DB_COLLATE', 'utf8_general_ci');

in my wp-config.php... I have everything exactly the same like in the link provided, except the function name, and I have different SQL fields... How to make the newly created table utf8 by default?

this is my function:

function duende_install() {
global $wpdb;
global $duende_db_version;

$table_name = $wpdb->prefix . "duendes";
if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
    $sql = "CREATE TABLE " . $table_name . " (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        shape tinytext NOT NULL,
        integrity tinytext NOT NULL,
        length tinytext NOT NULL,
        drone tinytext NOT NULL,
        wood tinytext NOT NULL,
        mouth tinytext NOT NULL,            
        rim tinytext NOT NULL,
        bell tinytext NOT NULL,                     
        loud tinytext NOT NULL,                     
        mass tinytext NOT NULL,                     
        finish tinytext NOT NULL,   
        inlay tinytext NOT NULL,                    
        price smallint DEFAULT '0' NOT NULL,
        sold tinytext NOT NULL,                                             
        UNIQUE KEY id (id)
    );";

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

    add_option("duende_db_version", $duende_db_version);
}
}

Thank you

share|improve this question
    
Please add your CREATE TABLE SQL statement to your question, probably with the related PHP code. –  hakre Dec 25 '10 at 20:39
    
here, I put the whole function in my question –  pootzko Dec 25 '10 at 20:47
2  
Okay, it shows that you do not specify the COLLATION while creating the table. It will inherit the collation from the database which is latin1_swedish_ci. Wordpress sets the collation only for the connection, not for the database. E.g. for WHERE or ORDER BY clauses. –  hakre Dec 26 '10 at 2:32
add comment

2 Answers

up vote 4 down vote accepted

You're missing the point that there is collation not only for the DB but as well for tables and even fields.

Therefore from your point of view, I assume that your CREATE TABLE statement is "not complete". Before using SQL statements, please learn the language first.

See CREATE TABLE Syntax (MySQL Manual).

Especially table_options / table_option and [DEFAULT] COLLATE [=] collation_name there in.

UPDATE:

Please see Wordpress Database Charset and Collation Configuration for an in-depth description where and how to setup wordpress regarding charset and collation.

share|improve this answer
    
well, I read somewhere in MySQL documentation that tables should by default inherit collation from the database... and here in my case - it didn't happen. that's why I asked... I can't find it again.. so, it's not inheritable and I should use COLLATE option in my CREATE TABLE statement? –  pootzko Dec 25 '10 at 21:15
1  
@pootzko: I don't think that you have a special database that is not acting like the manual says. Your database's collation is latin1_swedish_ci. You can verify this anytime with the mysql commandline tool or phpmyadmin. So inheration does work. See dev.mysql.com/doc/refman/5.0/en/charset-table.html and dev.mysql.com/doc/refman/5.0/en/charset.html . Only by throwing some commands on each connect does not change the collation of the DB, it changes it only for the connection. See dev.mysql.com/doc/refman/5.0/en/charset-connection.html :SET NAMES + COLLATION, used by WPDB –  hakre Dec 26 '10 at 2:30
1  
sorry, I now see that the default DB collation is indeed latin1_sweedish_ci.. since I saw all wp tables as utf8 in phpmyadmin I thought DBs collation would also be utf8 and I really thought I set the whole DB to be utf8... but instead it was WP who takes care of those basic tables for us if we don't set the DBs collation right.. I just wasn't careful enough, sorry. and thank you hakre –  pootzko Dec 26 '10 at 9:40
add comment

just in case somebody does need this, you could set the collation this way.. the row after the UNIQUE KEY id (id) part should look like this:

) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
share|improve this answer
1  
I now blogged about it: hakre.wordpress.com/2010/12/26/… --- this has some info how to keep this clean from the beginning of the installation as well. –  hakre Dec 26 '10 at 13:44
2  
If you do this in a plugin, it might be better to use th $wpdb->charset and $wpdb->collate settings, so other users of your plugin can override this if they have a good reason. See the first lines for wp-admin/includes/schema.php. –  Jan Fabry Dec 27 '10 at 8:23
1  
Thanks for actually answering this question with a real usable quick answer unlike the selected answer which rudely tells you to go read a ####ing manual –  jasondavis May 25 '13 at 0:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.