dbDelta isn't altering the table

Question

I'm trying to add a field Order to my tables:

$tables = array(Gun => 6,Color => 2,Holster => 2,Canter => 1,Hand => 1);
foreach($tables as $key => $val) {
    $table_name = $wpdb->prefix . 'GunInventory'.$key;
    $sql = "CREATE TABLE $table_name (
            GunInventory".$key."Id int(11) NOT NULL AUTO_INCREMENT,
            Name varchar(200) NOT NULL,
            Value char(".$val.") NOT NULL,
            Price decimal(10,2) NOT NULL,
            Order INT NOT NULL,
            UNIQUE KEY  GunInventory".$key."Id (GunInventory".$key."Id)
        );";
    dbDelta($sql);
}

The issue is the field Order is not being created on my tables, I've tried deleting the field from the tables and reinstalling the plug-in and my tables stay the same. The script will though create the tables.

I tried turning on debug and I don't get any errors.

Answer 1

Order is a reserved word.

List of all reserved words:

http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html

Answer 2

ORDER is a reserved keyword in MYSQL. Either escape it using backticks (`) or choose a different name. See this post for more information.

Also you should have a warning on your page if you enable error reporting unless those keys in your array are constants.

Notice: Use of undefined constant Gun - assumed 'Gun' in ...
Notice: Use of undefined constant Color - assumed 'Color' in ...
etc

http://3v4l.org/8PLuh

The reason it works is that those keys are being converted by PHP, but you should really quote them and enable error reporting.

Answer 3

Order is a mysql reserved keyword. Its supposed not to be used.

Your array definition seems to be not perfect. You are assigning keys as constants. That is not a good practice.

http://www.php.net/manual/en/language.types.array.php

$tables = array(Gun => 6,Color => 2,Holster => 2,Canter => 1,Hand => 1);

should be

$tables = array("Gun" => 6,"Color" => 2,"Holster" => 2,"Canter" => 1,"Hand" => 1);