My plugin won't create table in wordpress 3.5

Question

Hi I have this function to install the plugin and create database table:

    function sortresult_install()
{
    //install sort_search_result options to the database
    global $wpdb;
    $sortsearchtitle_db_version = '1.0';
    $table = $wpdb->prefix."sortsearchresult";
    $structure = "CREATE TABLE IF NOT EXISTS $table (id INT(9) NOT NULL AUTO_INCREMENT,order_valuex VARCHAR(5) NOT NULL,UNIQUE KEY id (id));";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($structure);
    //Save database version
    update_option("sortsearchtitle_db_version", $sortsearchtitle_db_version);
    // Insert default sorting setting that is ascending
    $wpdb->query("INSERT IGNORE INTO $table(order_valuex) VALUES('ASC')");
}   

And this is the construct function:

function __construct()
{

    //Register activation hook
    register_activation_hook(__FILE__,array(&$this,'sortresult_install'));

    //Add menu hook
    add_action( 'admin_menu', array(&$this,'sort_searchresults_menu'));

    //Update function hook
    add_action('plugins_loaded', array(&$this,'sortsearchtitle_update_db_check'));

    //Run the search function
    add_action('pre_get_posts',array(&$this,'sort_searchresult_by_title'));

}

Any ideas why the above functions won't create the database table? I am updating this plugin for Wordpress 3.5.Thanks.

Answer 1

First of all I want to reference you to the WP codex article about Creating or Updating the Table with Plugin. Note that the dbDelta function is rather picky, however. For instance:

• You must put each field on its own line in your SQL statement.
• You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
• You must not use any apostrophes or backticks around field names.

It means that the issue is in your table declaration, which is stored in $structure variable. Try to rewrite it in the following way:

$structure = "CREATE TABLE $table (
  id INT(9) NOT NULL AUTO_INCREMENT,
  order_valuex VARCHAR(5) NOT NULL,
  UNIQUE KEY id (id)
);";

And then your code will look like:

function sortresult_install()
{
    //install sort_search_result options to the database
    global $wpdb;

    $sortsearchtitle_db_version = '1.0';
    $table = $wpdb->prefix."sortsearchresult";
    $structure = "CREATE TABLE $table (
      id INT(9) NOT NULL AUTO_INCREMENT,
      order_valuex VARCHAR(5) NOT NULL,
      UNIQUE KEY id (id)
    );";

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

    //Save database version
    update_option("sortsearchtitle_db_version", $sortsearchtitle_db_version);

    // Insert default sorting setting that is ascending
    $wpdb->query("INSERT IGNORE INTO $table(order_valuex) VALUES('ASC')");
}