What is my error trying to create a new table in the database?

Question

I tried creating the tables in different ways and I always get the same error: "Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ...... at line 1 of the WordPress database for the CREATE TABLE IF NOT query EXISTS"

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    
    global $wpdb, $rs_plugin_db_version, $table_name_clientes, $table_name_promociones;
    
    $charset_collate = $wpdb->get_charset_collate();
    
    $wpdb->query(
        "CREATE TABLE IF NOT EXISTS $table_name_clientes (
            id int(11) NOT NULL,
            logo text NOT NULL,
            name text NOT NULL,
            whatsapp text DEFAULT '' NOT NULL,
            instagram text DEFAULT '' NOT NULL,
            facebook text DEFAULT '' NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate ENGINE = InnoDB"
    );
    dbDelta();
    
    $wpdb->query(
        "CREATE TABLE IF NOT EXISTS $table_name_promociones (
            id int(11) NOT NULL,
            img text NOT NULL,
            title text NOT NULL,
            content text NOT NULL,
            owner int(11) NOT NULL,
            contact int(11) NOT NULL,
            PRIMARY KEY  (id),
            FOREIGN KEY  (owner) REFERENCES $table_name_clientes(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) $charset_collate ENGINE = InnoDB"
    );
    dbDelta();

In one of the tables I need to create a foreign key, if I didn't misunderstood dbDelta() does not support them and therefore my last attempt was with $wpdb->query, but with or without foreign key the result is the same.

maybe my mistake is obvious, but honestly I can't find it

Answer 1

That's not how you create tables with dbDelta. dbDelta expects an SQL statement as its argument, but the code in your question passes none. What's more, the SQL is passed into wpdb->query, and has no checks or sanitisation. A quick run of PHPCS would reveal warnings that no prepare call is used.

Instead, the official documentation at https://developer.wordpress.org/reference/functions/dbdelta/ has this example:

global $wpdb;
$table_name = $wpdb->prefix . 'dbdelta_test_001';
$wpdb_collate = $wpdb->collate;
$sql =
"CREATE TABLE {$table_name} (
    id mediumint(8) unsigned NOT NULL auto_increment ,
    first varchar(255) NULL,
    PRIMARY KEY  (id),
    KEY first (first)
    )
    COLLATE {$wpdb_collate}";
 
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta( $sql );

Note however, that dbDelta has extremely specific formatting requirements. For example, there must be 2 spaces after PRIMARY KEY. dbDelta will also adjust existing tables to match the statement, and it will create the table if it doesn't exist

Also keep in mind that in a lot of cases custom post types are easier and more performant. In this case, a CPT + taxonomy might be more performant in some circumstances due to the absence of indexes on the custom tables, and the loss of the internal WP_Cache system

Answer 2

Finally I found the solution, and as I expected, the error was stupid, simply for some reason that I unknow, the names of the tables must be defined obligatorily within the function, even if you use global $ ... it won't work, you have to define them inside.

The correct function was something like this:

$table_name_clientes = $wpdb->prefix . 'clientes';
$table_name_promociones = $wpdb->prefix . 'promociones';

$charset_collate = $wpdb->get_charset_collate();

$sql_clientes = "CREATE TABLE IF NOT EXISTS $table_name_clientes (
    id tinyint NOT NULL,
    logo text NOT NULL,
    name text NOT NULL,
    whatsapp text DEFAULT '' NOT NULL,
    instagram text DEFAULT '' NOT NULL,
    facebook text DEFAULT '' NOT NULL,
    PRIMARY KEY  (id)
) $charset_collate ENGINE = InnoDB;";

$sql_promociones = "CREATE TABLE IF NOT EXISTS $table_name_promociones (
    id tinyint NOT NULL,
    img text NOT NULL,
    title text NOT NULL,
    content text NOT NULL,
    owner tinyint NOT NULL,
    contact tinyint NOT NULL,
    PRIMARY KEY  (id),
    FOREIGN KEY  (owner) REFERENCES $table_name_clientes(id) ON DELETE CASCADE ON UPDATE CASCADE
) $charset_collate ENGINE = InnoDB;";

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