0

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.

2

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 ..." );
| improve this answer | |
2

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.

| improve this answer | |

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.