Error creating table: You have an error in your SQL syntax;

Question

I'm trying to make some tables in my database using this.

but I get this error:

Error creating table: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNSIGNED AUTO_INCREMENT, ip VARCHAR(65) NOT NULL, attempts INT(11) ' at line 2

on this:

$dbprefix = 'duh_';
$prefixloginAttempts = $dbprefix . 'loginAttempts';

$table = array();

$table[] = "CREATE TABLE IF NOT EXISTS $prefixloginAttempts (
    id              INT(11)     NOT NULL UNSIGNED AUTO_INCREMENT,
    ip              VARCHAR(65) NOT NULL,
    attempts        INT(11)     NOT NULL,
    lastLogin       DATETIME    NOT NULL,
    username        VARCHAR(65) NOT NULL,
    mod_timestamp   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY                     ('id'),
    UNIQUE KEY 'id_UNIQUE'          ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

foreach ($table as $sql) {
    $query = $conn->query($sql);
}

Someone that can see where the fail is? I cant..

score 1 · Accepted Answer · answered Oct 26 '17 at 18:36

The error messages provided by MySQL contain the part of the query where the parsing stopped. The error is usually either on the first word from the part of the query listed in the ... near "..." message or just before it.

On this query, the problem is on the UNSIGNED keyword. It is part of the data type and it must stay right after with INT(11):

id              INT(11)     UNSIGNED NOT NULL AUTO_INCREMENT,

Read about the syntax of the CREATE TABLE statement.

There are also errors on the definitions of indexes. The column names are enclosed in apostrophes and this makes them strings and not object names.

The object names can be left unquoted or, if they are also SQL keywords or MySQL reserved words then they must be enclosed in backticks (`):

PRIMARY KEY                     (id),
UNIQUE KEY id_UNIQUE            (id)

or

PRIMARY KEY                     (`id`),
UNIQUE KEY `id_UNIQUE`          (`id`)

Read when to use single quotes, double quotes or backticks in MySQL.

Thanks! You saved me from a lot of pain! :) – Linus Karlsson Oct 26 '17 at 18:58 — Linus Karlsson, Oct 26 '17 at 18:58

Error creating table: You have an error in your SQL syntax;

1 Answers1