CODEWITHSUNDEEP
wordpresspluginsactivation-function
Ross Crawford
Ross Crawford

Reputation: 33

Wordpress dbDelta tables not being created

I'm trying to execute the following code via register_activation_hook. But when I activate the plugin, only the first table is created.

function fsec_create_tables(){
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $sql = array();
    
    $table_name = $wpdb->prefix . 'fsec_keys';
    $sql[] = "CREATE TABLE {$table_name} (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        data_key varchar(255) NOT NULL,
        is_group BOOLEAN NOT NULL DEFAULT 0,
        is_active BOOLEAN NOT NULL DEFAULT 0,
        is_master BOOLEAN NOT NULL DEFAULT 0,
        last_id bigint(20) NOT NULL DEFAULT 0,
        update_hours tinyint NOT NULL DEFAULT 1,
        last_updated datetime,
        PRIMARY KEY  (id)
    ) {$charset_collate};";
    
    $table_name = $wpdb->prefix . 'fsec_log_flight';
    $sql[] = "CREATE TABLE {$table_name} (
        id bigint(20) NOT NULL,
        type varchar(20),
        time datetime,
        distance smallint,
        pilot varchar(255),
        serial_number bigint,
        aircraft varchar(50),
        make_model varchar(255),
        from varchar(20),
        to varchar(20),
        total_engine_time datetime,
        flight_time datetime,
        group_name varchar(255),
        income decimal(12,2),
        pilot_fee decimal(12,2),
        crew_cost decimal(12,2),
        booking_fee decimal(12,2),
        bonus decimal(12,2),
        fuel_cost decimal(12,2),
        gcf decimal(12,2),
        rental_price decimal(12,2),
        rental_type varchar(10),
        rental_units datetime,
        rental_cost decimal(12,2),
        PRIMARY KEY  (id)
    ) {$charset_collate};";
    
    $table_name = $wpdb->prefix . 'fsec_log_payment';
    $sql[] = "CREATE TABLE {$table_name} (
        id bigint(20) NOT NULL,
        date datetime,
        to varchar(255),
        from varchar(255),
        amount decimal(12,2),
        reason varchar(255),
        fbo varchar(20),
        location varchar(20),
        aircraft varchar(50),
        comment varchar(255),
        aircraft_id bigint,
        PRIMARY KEY  (id)
    ) {$charset_collate};";
    
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}

However, if I reduce tables 2 & 3 to a single field each, it works fine. If I then replace all the fields, activating again correctly updates the definitions of tables 2 & 3!

But if I drop the tables, it goes back to not working.

I know I can just create the tables using $wpdb directly, and may end up doing that, but it'd be nice to be able to get the dbDelta working. Anyone have any ideas?

Upvotes: 0

Views: 209

Answers (2)

MTP
MTP

Reputation: 171

From and to are reserved keywords. You can escape these with backticks:

`from`

The correct CREATE TABLE syntax should look like this:

$sql[] = "CREATE TABLE {$table_name} (
    id bigint(20) NOT NULL,
    date datetime,
    `to` varchar(255),
    `from` varchar(255),
    amount decimal(12,2),
    reason varchar(255),
    fbo varchar(20),
    location varchar(20),
    aircraft varchar(50),
    comment varchar(255),
    aircraft_id bigint,
    PRIMARY KEY  (id)
) {$charset_collate};";

You can validate the script syntax with online tools or check for reserved keywords in the database documentation:

Upvotes: 0

Ross Crawford
Ross Crawford

Reputation: 33

OK, turns out, even the update worked, it was the "from" and "to" fields causing the problem. Gave them better names and it works great.

Upvotes: 0

Related Questions