I have an activation hook to create 2 new database tables that don't exist, only the second table is created from this code:

    public function add_tables() {

        // Global $wpdb

        global $wpdb;
        $wpdb->hide_errors();

        // Require upgrade

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

        // Set charset

        $collate = '';
        if ( $wpdb->has_cap( 'collation' ) ) {
            $collate = $wpdb->get_charset_collate();
        }

        // SQL query

        $sql = "
        CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test1 (
            test_id bigint(20) NOT NULL AUTO_INCREMENT,
            test_key char(64) NOT NULL,
        ) $collate;
        CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test2 (
            test_id bigint(20) NOT NULL AUTO_INCREMENT,
            test_key char(64) NOT NULL,
        ) $collate;
        ";

        // Do SQL

        dbDelta( $sql );

    }

Why only the second? If I print out the $sql variable I get the SQL statement and if I run that in phpMyAdmin it creates the 2 tables.

I looked at how plugins like WooCommerce do it (.php#L687) and it appears I am doing the same as they are.

I have an activation hook to create 2 new database tables that don't exist, only the second table is created from this code:

    public function add_tables() {

        // Global $wpdb

        global $wpdb;
        $wpdb->hide_errors();

        // Require upgrade

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

        // Set charset

        $collate = '';
        if ( $wpdb->has_cap( 'collation' ) ) {
            $collate = $wpdb->get_charset_collate();
        }

        // SQL query

        $sql = "
        CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test1 (
            test_id bigint(20) NOT NULL AUTO_INCREMENT,
            test_key char(64) NOT NULL,
        ) $collate;
        CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test2 (
            test_id bigint(20) NOT NULL AUTO_INCREMENT,
            test_key char(64) NOT NULL,
        ) $collate;
        ";

        // Do SQL

        dbDelta( $sql );

    }

Why only the second? If I print out the $sql variable I get the SQL statement and if I run that in phpMyAdmin it creates the 2 tables.

I looked at how plugins like WooCommerce do it (https://github.com/woocommerce/woocommerce/blob/c04f7b79f972ee854e5f5d726eb78ac04a726b32/includes/class-wc-install.php#L687) and it appears I am doing the same as they are.

• plugins
• database
• sql
• activation

3 Answers 3

Why only the second?

Because dbDelta() supports CREATE TABLE table_name format only. I.e. Exactly "CREATE TABLE" followed by (one space and) the table name.

More specifically, dbDelta() uses this regular expression pattern: CREATE TABLE ([^ ]*) when parsing the queries into an array indexed by the table name; i.e. array( 'table_1' => 'query', 'table_2' => 'query', ... ).

Here's the relevant code:

$cqueries   = array(); // Creation Queries
...

// Create a tablename index for an array ($cqueries) of queries
foreach ( $queries as $qry ) {
    if ( preg_match( '|CREATE TABLE ([^ ]*)|', $qry, $matches ) ) {
        $cqueries[ trim( $matches[1], '`' ) ] = $qry;
        $for_update[ $matches[1] ]            = 'Created table ' . $matches[1];
    }
    ...
}

So in the case of CREATE TABLE IF NOT EXISTS table_name, the table name is (seen as) IF and not table_name due to the regular expression pattern:

preg_match( '|CREATE TABLE ([^ ]*)|', 'CREATE TABLE IF NOT EXISTS table_name', $matches );
var_dump( $matches );
/* Output:
array(2) {
  [0]=>
  string(15) "CREATE TABLE IF"
  [1]=>
  string(2) "IF"
}
*/

preg_match( '|CREATE TABLE ([^ ]*)|', 'CREATE TABLE table_name', $matches );
var_dump( $matches );
/* Output:
array(2) {
  [0]=>
  string(23) "CREATE TABLE table_name"
  [1]=>
  string(10) "table_name"
}
*/

And in your case, both queries do match that pattern, but since the table names are both (seen as) IF, the first/previous array item ($queries['IF']) is then overridden. Hence only the second table (the one in the final value of $queries['IF']) gets created.

And WooCommerce actually do not have CREATE TABLE IF NOT EXISTS in their code:

https://github.com/woocommerce/woocommerce/blob/c04f7b79f972ee854e5f5d726eb78ac04a726b32/includes/class-wc-install.php#L687

Possible Solution when using the CREATE TABLE IF NOT EXISTS table_name format

Call dbDelta() for each of the queries, as in @FahamShaikh's answer:

$queries = [ // array of queries
    "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test1 ...",
    "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test2 ...",
];

foreach ( $queries as $sql ) {
    dbDelta( $sql );
}

Or:

dbDelta( "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test1 ..." );

dbDelta( "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test2 ..." );

One way to get it to work would be to do it like this:

global $wpdb;
$wpdb->hide_errors();
// Require upgrade
// Set charset
$collate = '';
if ( $wpdb->has_cap( 'collation' ) ) {
    $collate = $wpdb->get_charset_collate();
}
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
$queries = array();
array_push($queries, "
CREATE TABLE IF NOT EXISTS {$wpdb->prefix}test1 (
       `test_id1` bigint(20) NOT NULL AUTO_INCREMENT,
       `test_key1` char(64) NOT NULL,
       `test_key21` char(64) NULL, PRIMARY KEY  (`test_id1`)
    ) {$collate}");
array_push($queries, "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}test2 (
        `test_id2` bigint(20) NOT NULL AUTO_INCREMENT,
        `test_key2` char(64) NOT NULL,
        `test_key12` char(64) NULL, PRIMARY KEY  (`test_id2`)
    ) {$collate}");
foreach ($queries as $key => $sql) {
    dbDelta( $sql );
}

I agree that this doesn't actually explains why the supported format for dbdelta is not working but this seems to work at the very least.

Here's another way that worked for me which I found here:

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

$sql = 'CREATE TABLE table1...';
dbDelta ($sql);

$sql = 'CREATE TABLE table2...';
dbDelta ($sql);

$sql = 'CREATE TABLE table3...';
dbDelta ($sql);

Here's how I used it in a plugin:

/**
 * register_activation_hook implementation
 */
if (!function_exists('custom_install')) {
    function custom_install()
    {
        global $wpdb;
        global $custom_db_version;

        $table_name = $wpdb->prefix . 'customers';

        $table_name2 = $wpdb->prefix . 'favorite';

        // sql to create your table
        $sql = "CREATE TABLE " . $table_name . " (
            id int(11) NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            subject VARCHAR(255) NOT NULL,
            message text,
            amount VARCHAR(255),
            status VARCHAR(10),
            PRIMARY KEY (id)
        );";

        $sql2 = "CREATE TABLE " . $table_name2 . " (
            `color` bigint(12) NOT NULL,
            `size` varchar(20) NOT NULL,
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1";

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

        // save current database version 
        add_option('custom_db_version', $custom_db_version);
    }

    register_activation_hook(__FILE__, 'custom_install');
}