Creating a table in Wordpress using the dbDelta function
I need to create a custom table in wordpress for a plugin. I followed several online texts and created a spreadsheet but found it was impossible to get it. When trying to select everything from the table, nothing will be returned, and when trying to view the table using the database browser plugin, I got this error: "You have an error in your SQL syntax, check the manual corresponding to your MySQL server version for the correct syntax to use next to "FROM wp-typeEvents LIMIT 0, 100" on line 1 "in response to the plugin request (" SELECT SQL_CALC_FOUND_ROWS FROM wp-typeEvents LIMIT 0, 100 ;; ").
Long story short, I am trying to use dbDelta to create a table. The table is created, but there is some problem that it cannot add rows or read their contents.
I read that dbDelta can be a finishing function, so I tried to stick to three golden rules:
- Filling each field on a new line
-Translating two spaces between the primary key and its definition
-If at least one key
Here's the code:
global $wpdb;
$tablename = "wp-typeEvents";
$query = "CREATE TABLE `" . $tablename . "` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`eventName` varchar(60) NOT NULL,
`location` varchar(60) DEFAULT '' NULL,
`price` double NOT NULL,
`description` text NOT NULL,
`paypal` varchar(60) NOT NULL,
PRIMARY KEY (`id`)
);";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($query);
Any ideas?
source to share
Don't recode the table name, use the $ wpdb-> prefix (as someone else pointed out).
Don't use quotation marks around field names.
There are other useful "rules" that are all listed here: http://codex.wordpress.org/Creating_Tables_with_Plugins
Please note that the dbDelta function is quite complex. For example:
- You have to put each field on its own line in your SQL statement.
- You must have two spaces between the words PRIMARY KEY and your primary key definition.
- You must use the KEY keyword, not the INDEX synonym, and you must include at least one KEY.
- You shouldn't use any apostrophes or backreferences around field names.
- Field types must be lowercase.
- SQL keywords such as CREATE TABLE and UPDATE must be uppercase.
source to share
Also note that the current function dbDelta()
does not parse order keywords ASC | DESC on the UNIQUE KEY name of the column it refers to adds a constraint to the remaining index, and therefore will try to add a constraint causing a Unique Key error. See example below:
Try to create this questrong will create an error.
CREATE TABLE wp_test (
id int(11) NOT NULL AUTO_INCREMENT,
email varchar(100) NOT NULL,
PRIMARY KEY (stcr_id),
UNIQUE KEY uk_email (subscriber_email ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET utf8;
By default, the order type is ASC, so this will get rid of.
CREATE TABLE wp_test (
id int(11) NOT NULL AUTO_INCREMENT,
email varchar(100) NOT NULL,
PRIMARY KEY (stcr_id),
UNIQUE KEY uk_email (subscriber_email))
ENGINE = InnoDB
DEFAULT CHARACTER SET utf8;
I tested this on WordPress 4.1.1
source to share
I ran into little problems while using dbDelta functionality for wordpress and decided to create a function for it:
/**
* 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);
}
source to share