WordPress plugin table, cannot create with dbDelta although sql is correct

4 comments

1. Ok, so you have illegal characters in your comment in the CREATE TABLE in your gallery_select_by column

   Try with:

   global $wpdb;
   $table_name = $wpdb->prefix . "ajax_preview_galleries";
   $charset_collate = $wpdb->get_charset_collate();
   
   //Table definition
   $sql =  "CREATE TABLE $table_name (
   gallery_id int(10) unsigned NOT NULL AUTO_INCREMENT,
   gallery_name varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   gallery_slug varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   gallery_selected_terms text COLLATE utf8_unicode_ci NOT NULL,
   gallery_select_by tinyint(3) unsigned NOT NULL COMMENT '0 - categories only. 1 - tags only. 2 - both',
   gallery_post_count tinyint(3) unsigned NOT NULL,
   gallery_custom_class_container varchar(200) COLLATE utf8_unicode_ci NOT NULL,
   gallery_custom_class_buttons varchar(200) COLLATE utf8_unicode_ci NOT NULL,
   gallery_transition_time int(10) unsigned NOT NULL DEFAULT '500',
   gallery_loading_type tinyint(3) unsigned NOT NULL DEFAULT '1',
   gallery_navigator_loop tinyint(3) unsigned NOT NULL DEFAULT '1',
   PRIMARY KEY  (gallery_id)
   ) $charset_collate;";
   
   require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
   
   $res = dbDelta($sql);
   

   The thing is, you had : and ; in your comment, and ; was probably signaling to end the sql statement, so you got errors.

   I tried searching escaping it, but only found this for string literals, nothing about colon and semicolon.

   Hope this helps.

2. Add this just before your dbDelta:

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

   You have to add that line in to explicitly load that portion of core you need to run dbDelta.

3. Got it to work!
   As it seems, the problems was the comment on one of the columns. In fact, I suspected as much, and I already tried to remove comments, although the one on the gallery_select_by column escaped my attention. Thanks to dingo_d for pointing my attention toward that line!

   The WordPress Codex does specify 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.
     Field types must be all lowercase.
   • SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
   • You must specify the length of all fields that accept a length parameter. int(11), for example.

   As it seems, these were merely some examples, and dbDelta() has something to complain even about comments. I wasn’t able to find a complete list of the “rules” of dbDelta, but at least I got my case to work.

   By the way: as mentioned earlier, I used to get this result from dbDelta() when the table was NOT being created

   Result: Array ( [orhub_ajax_preview_galleries] => Created table orhub_ajax_preview_galleries ) 
   

   Now that the plugin is working, instead, I am getting an empty array. Go figure!
   This seems rather peculiar to me as it is totally counter-intuitive (as other aspects of dbDelta()), so it’s probably good to know and I pointed it out for others who may battle with the same issues.

   It seems that, when dealing with dbDelta(), it must be taken into account that “special rules” apply and a query that works elsewhere may not work here (in fact, as I mentioned, my original sql worked for instance when put directly in phpMyAdmin). Results from the function may not be that heplful either…

4. You may want to try this function:

   $table_name = "ratings";
   
   $table_columns = "id INT(6) UNSIGNED AUTO_INCREMENT,
                       rate tinyint(1) NOT NULL,
                       ticket_id bigint(20) NOT NULL,
                       response_id bigint(20) NOT NULL,
                       created_at TIMESTAMP";
   
   $table_keys = "PRIMARY KEY (id),
                       KEY ratings_rate (rate),
                       UNIQUE KEY ratings_response_id (response_id)";
   
   create_table($table_name, $table_columns, $table_keys);
   
   /**
    * Prevents unnecessary re-creating index and repetitive altering table operations when using WordPress dbDelta function
    *
    * Usage Example:
    *
    * $table_name      = "ratings";
    *
    * $table_columns   = "id INT(6) UNSIGNED AUTO_INCREMENT,
    *          rate tinyint(1) NOT NULL,
    *          ticket_id bigint(20) NOT NULL,
    *          response_id bigint(20) NOT NULL,
    *          created_at TIMESTAMP";
    *
    * $table_keys      = "PRIMARY KEY (id),
    *          KEY ratings_rate (rate),
    *          UNIQUE KEY ratings_response_id (response_id)";
    *
    * create_table($table_name, $table_columns, $table_keys);
    *
    * Things that need to be considered when using dbDelta function :
    *
    * 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.
    * Field types must be all lowercase.
    * SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
    * You must specify the length of all fields that accept a length parameter. int(11), for example.
    *
    * Further information can be found on here:
    *
    * http://codex.wordpress.org/Creating_Tables_with_Plugins
    *
    * @param $table_name
    * @param $table_columns
    * @param null $table_keys
    * @param null $charset_collate
    * @version 1.0.1
    * @author Ugur Mirza Zeyrek
    */
   function create_table($table_name, $table_columns, $table_keys = null, $db_prefix = true, $charset_collate = null) {
       global $wpdb;
   
       if($charset_collate == null)
           $charset_collate = $wpdb->get_charset_collate();
       $table_name = ($db_prefix) ? $wpdb->prefix.$table_name : $table_name;
       $table_columns = strtolower($table_columns);
   
       if($table_keys)
           $table_keys =  ", $table_keys";
   
       $table_structure = "( $table_columns $table_keys )";
   
       $search_array = array();
       $replace_array = array();
   
       $search_array[] = "`";
       $replace_array[] = "";
   
       $table_structure = str_replace($search_array,$replace_array,$table_structure);
   
       $sql = "CREATE TABLE $table_name $table_structure $charset_collate;";
   
       // Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/includes/upgrade.php (we'll have to load this file, as it is not loaded by default)
       require_once (ABSPATH . 'wp-admin/includes/upgrade.php');
   
       // The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary
       return dbDelta($sql);
   }
   

Comments are closed.