13

I went through a lot of threads, codex page and tried messing with a lot of things but my code doesn't seem to be creating the tables. And I am not able to figure out where I am going wrong. I checked booking_db_version in the database, it gets updated when I update it in the file.

Here's the code

global $booking_db_version;
$booking_db_version = "1.0.0";

function booking_install() {
    global $wpdb;
    global $booking_db_version;
    global $tableprefix;
    $installed_version = get_option('booking_db_option');

    $tableprefix = $wpdb->prefix . 'booking_';

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

    if ( $installed_version !== $booking_db_version ) {
        /* Create table for packages */
        $packagetable = $tableprefix . 'packages';
        $sql = "create table  $packagetable (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            description text NOT NULL, 
            city1 text NOT NULL, 
            city2 text NOT NULL,
            PRIMARY KEY  (id)
        );";
        dbDelta($sql);

        /* Create table for hotels */
        $hoteltable = $tableprefix . 'hotels';
        $sql = "create table $hoteltable (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            city text NOT NULL, 
            price decimal(10,2) NOT NULL,
            PRIMARY KEY  (id)
        );";
        dbDelta($sql);

        /* Create table for addons */
        $addontable = $tableprefix . 'addons';
        $sql = "create table $addontable (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            addongroup text NOT NULL, 
            price decimal(10,2) NOT NULL,
            PRIMARY KEY  (id)
        );";
        dbDelta($sql);

        /* Create table for addon groups */
        $addongrouptable = $tableprefix . 'addon_groups';
        $sql = "create table $addongrouptable (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            perhead text NOT NULL,
            PRIMARY KEY  (id)
        );";
        dbDelta($sql);

        update_option('booking_db_version', $booking_db_version);
    }
}
register_activation_hook(__FILE__, 'booking_install');
15

From WordPress-codex about dbDelta:

The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates (see wp-admin/upgrade-schema.php for more examples of how to use dbDelta). 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.

With those caveats, here are the next lines in our function, which will actually create or update the table. You'll need to substitute your own table structure in the $sql variable.

I changed your sql: "create table $packagetable (

To this: "CREATE TABLE " . $packagetable . " (

Here is a working copy of your code:

global $booking_db_version;
$booking_db_version = "1.0.0";

function booking_install() {
    global $wpdb;
    global $booking_db_version;
    global $tableprefix;
    $installed_version = get_option('booking_db_option');

    $tableprefix = $wpdb->prefix . 'booking_';

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

    if ( $installed_version !== $booking_db_version ) {
        // Create table for packages 
        $packagetable = $tableprefix . 'packages';
        $sql = "CREATE TABLE " . $packagetable . " (
            id INT NOT NULL AUTO_INCREMENT, 
            name TEXT NOT NULL, 
            description TEXT NOT NULL, 
            city1 TEXT NOT NULL, 
            city2 TEXT NOT NULL,
            PRIMARY KEY  (id)
        ) ". $charset_collate .";";
        dbDelta($sql);

        // Create table for hotels 
        $hoteltable = $tableprefix . 'hotels';
        $sql = "CREATE TABLE " . $hoteltable . " (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            city text NOT NULL, 
            price decimal(10,2) NOT NULL,
            PRIMARY KEY  (id)
        ) ". $charset_collate .";";
        dbDelta($sql);

        // Create table for addons 
        $addontable = $tableprefix . 'addons';
        $sql = "CREATE TABLE " . $addontable . " (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            addongroup text NOT NULL, 
            price decimal(10,2) NOT NULL,
            PRIMARY KEY  (id)
        ) ". $charset_collate .";";
        dbDelta($sql);

        // Create table for addon groups 
        $addongrouptable = $tableprefix . 'addon_groups';
        $sql = "CREATE TABLE " . $addongrouptable . " (
            id mediumint(9) NOT NULL AUTO_INCREMENT, 
            name text NOT NULL, 
            perhead text NOT NULL,
            PRIMARY KEY  (id)
        ) ". $charset_collate .";";
        dbDelta($sql);

        update_option('booking_db_version', $booking_db_version);
    }
}
register_activation_hook(__FILE__, 'booking_install');
  • 1
    That did work. I read that dbDelta is finicky but didn't realize that not capitalizing CREATE TABLE will cause it to fail. – mehulved Dec 27 '12 at 5:21
  • 1
    Although Wordpress doesn't say this on their codex page, you can't have a trailing comma on the last line. Example: PRIMARY KEY (id),. dbDelta actually says that it creates the table even though it doesn't – JoeMoe1984 May 8 '15 at 18:57
  • Just for reference, the issue with the trailing comma, PRIMARY KEY (id), is a SQL issue, not dbDelta nor a WP issue. Hence no documentation. – Jeremy Oct 16 '15 at 16:19
  • Note that when creating multiple queries with dbDelta() you can pass your SQL's as an array to dbDelta instead of calling dbDelta individually for every query. – toni_lehtimaki Oct 11 '17 at 11:37
1

You can 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);
0

Using 'CREATE TABLE' instead of 'create table' solved the problem for me.

0

Besides all those important points, you should trigger the activation hook.

While you developed your plugin and wrote correct code, you still need to reactivate your plugin to trigger the hook, so your table will be created when the plugin is activated.

-2

SQL keywords, like CREATE TABLE and UPDATE, must be uppercase. so change line of create table to :

"CREATE TABLE " . $packagetable . "( 

and

id mediumint(9) NOT NULL AUTO_INCREMENT,

to:

id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,

or this:

name text NOT NULL, 

to:

name TEXT NOT NULL, 

and so on

  • "SQL keywords, like […], must be uppercase". Sorry, but no, this is not true. – kaiser Jan 8 '17 at 13:52
  • for using dbDelta function , we should use uppercase. please check this page: codex.wordpress.org/Creating_Tables_with_Plugins – shirin niki Jan 8 '17 at 13:56
  • Sorry, but I can read that nowhere in the source. Did I miss something? Maybe you want to add an example mini-plugin to your answer that shows that it fails (which someone can take and test) with lower cased syntax? – kaiser Jan 8 '17 at 13:59
  • in this link: link , the first paragraph of Creating or Updating the Table mention this issue. – shirin niki Jan 8 '17 at 14:13
  • This is only true for CREATE TABLE, CREATE DATABASE, INSERT INTO, and UPDATE. Everything else is either not used in a case-sensitive comparison or converted to lower case. Your suggestions have no effect. – fuxia Jan 8 '17 at 14:52

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.