How to create a database table?

Question

There are several things going on here

Firstly, your dbDelta call never receives a CREATE SQL statement. $sql is only defined if the if statement is true, and if your developer environment is setup correctly then running this code should generate PHP Warnings. You’re actually running dbDelta('');

dbDelta already does this check though. It’s not for creating tables, but for updating their schema, hence the ‘delta’ part of the name, so an immediate improvement would be:

// not guaranteed to work
function activate() {
    global $wpdb;
    $table_name = $wpdb->prefix . "event";
    $sql = "CREATE TABLE" . $table_name . "(
  id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
  dateOfEvent DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
  eventName VARCHAR(100) DEFAULT ''  NOT NULL,
  PRIMARY KEY (id)
) $charset_collate";
    require_once(ABSPATH . "wp-admin/includes/upgrade.php");
    dbDelta($sql);
}

Finally, the SQL statement may be valid, but it is not formatted correctly.

Googling dbDelta reveals this codex page as the second result, with the following instructions:

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.

KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.

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.

From this we see that:

Your field types are all uppercase, dbDelta requires they are all lowercase
You only put a single space between the PRIMARY KEY and the (id)

Here is the example in the codex:

global $wpdb;

$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  name tinytext NOT NULL,
  text text NOT NULL,
  url varchar(55) DEFAULT '' NOT NULL,
  PRIMARY KEY  (id)
) $charset_collate;";

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

dbDelta is EXTREMELY picky and specific about its input. Just because it’s valid SQL doesn’t mean it’s valid dbDelta input. Follow the guidelines and instructions very carefully.

A SideNote

Create a custom post type named event and use the post publish date instead of dateOfEvent. Use a pre_get_posts filter to modify which posts are shown so that future posts are displayed

This will be significantly easier to do, uses the caching system in WordPress, give you URLs are archives out of the box, a free GUI, import/export support, and there’s tonnes of plugins that have already done this you can use.

Never use custom tables and raw SQL statements if you can use a custom post type

A SideNote

Related Posts: