0

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

1
  • That's not how dbDelta works or what the examples look like. dbDelta takes a create table sql statement as a parameter, you shouldn't be making queries with wpdb
    – Tom J Nowell
    Oct 20, 2020 at 11:20

2 Answers 2

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

1
  • Yes I know, it is not the correct way to use dbDelta, the code remained like this because, as I said, that had been my last attempt and I extracted it from the internet without too many details because I was already somewhat frustrated haha. Today I took it more calmly and the solution was stupid, simply the names of the tables must be defined obligatorily within the function. Oct 20, 2020 at 20:17
-1

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 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.