0

I've looked through different sources but I still can't seem to update my database.

Or is it I've placed it wrongly?

I've placed an includes "createTable.php" at the beginning of my main plugin code.

At my createTable.php this is the code I wrote:

function activate() {
    global $wpdb;


    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . "event";
    if ($wpdb->get_var("SHOW TABLES LIKE " . $table_name) != $table_name) {
        $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);
}

register_activation_hook(__FILE__, "activate");

Could anyone tell me what I did wrong? Thanks.

  • dbDelta already checks if the table exists, it's intended for upgrading and modifying tables, and it's extremely picky about the input you give it and how it's formatted – Tom J Nowell Jun 3 '17 at 14:32
  • is there anything wrong with the way I formatted it? – petrichor-vellichor Jun 3 '17 at 15:07
  • I recommend looking up the documentation for dbDelta, it has extremely specific requirements that go beyond writing out well formed SQL, e.g. the number of spaces after PRIMARY KEY matters. Just because it's valid SQL doesn't mean dbDelta will accept it. Also the check if the table exists already shouldn't be necessary, and if the table already exists then $sql is empty and dbDelta is called with an empty string and a PHP warning – Tom J Nowell Jun 4 '17 at 3:49
  • I've read the documentation and changed my code accordingly but I still can't manage to create the table. – petrichor-vellichor Jun 4 '17 at 11:46
0

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

  • thanks, I've managed to get my code working after putting it in in the activation codes (i.e. the code that runs after you click activate on the plug-in page). – petrichor-vellichor Jun 15 '17 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.