Error with MySQL "CREATE TABLE" query

Question

I know that this has been asked hundreds of times before, but I looked around the Internet and couldn't find what could possibly be wrong with my syntax:

mysql_select_db('Projects');

        $sql = "CREATE TABLE $data[ID] (
            ID INT NOT NULL,
            Creator INT NOT NULL,
            Name VARCHAR(20) NOT NULL,
            Version VARCHAR(20) NOT NULL,
            Status VARCHAR(20) NOT NULL,
            Date VARCHAR(20) NOT NULL,
            Skript VARCHAR(20) NOT NULL,
            Filename VARCHAR(20) NOT NULL,
            Downloads INT NOT NULL,
            PRIMARY KEY(ID)
            )";

        if (mysql_query($sql)){

            echo "Created";

        }else{

            echo "Not created, ".mysql_error();

        }

I tried using backticks, quotation marks et.c but in vain. The table name (21) displayed in the error is correct.

Here's the error that I get, including the echo "Not created, ";:

Not created, 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 '23 ( ID INT NOT NULL, Creator INT NOT NULL, Name VARCHAR(20) NOT NUL' at line 1

What result do you get if you echo out $sql before running query? — bestprogrammerintheworld
– bestprogrammerintheworld, Commented Apr 20, 2013 at 14:04
The value of $data[ID] is 21, surrounding it in backticks worked! @bestprogrammerintheworld — SamTebbs33
– SamTebbs33, Commented Apr 20, 2013 at 14:17

echo_Me · Accepted Answer · 2013-04-20 14:10:29Z

6

Rules for naming objects, including tables in MySql:

http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

you cant name your table start with digits

this will work forexample

   $sql = "CREATE TABLE 't'.$data[ID] (
        ID INT NOT NULL,
        Creator INT NOT NULL,
        Name VARCHAR(20) NOT NULL,
        Version VARCHAR(20) NOT NULL,
        Status VARCHAR(20) NOT NULL,
        Date VARCHAR(20) NOT NULL,
        Skript VARCHAR(20) NOT NULL,
        Filename VARCHAR(20) NOT NULL,
        Downloads INT NOT NULL,
        PRIMARY KEY(ID)
        )";

as you see it starts by t

or use backticks around it. like that

   `$data[ID]`

edited Apr 20, 2013 at 14:10

answered Apr 20, 2013 at 14:04

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

echo_Me Over a year ago

it can be quoted by backticks

Barış Akkurt · Accepted Answer · 2013-04-20 14:04:48Z

0

Your query seems correct except the value of $data[ID]. You should check it.

I think the problem is with the value of the $data[ID] what is the value of it? Also, use mysqli_query() or PDO (as indicated by bestprogrammerintheworld) instead of the old mysql_query().

answered Apr 20, 2013 at 14:04

Barış Akkurt

2,2563 gold badges25 silver badges40 bronze badges

1 Comment

Barış Akkurt Over a year ago

I tried to keep simple answer to send it immediately. I edited now.

Álvaro González · Accepted Answer · 2013-04-20 14:07:39Z

0

You've managed to omit the most important piece of info, which is the SQL code that triggers the SQL error. But we can gather some hints from the PHP that generates it:

$sql = "CREATE TABLE $data[ID] (
            ID INT NOT NULL,

... and the error message:

near '23 ( ID INT NOT NULL

I suspect you try to run this SQL:

CREATE TABLE 23 (
    ID INT NOT NULL

If you actually want to create a table called 23 you need to quote it with backticks every single time you make use of it, including creation:

CREATE TABLE `23` (
    ID INT NOT NULL

answered Apr 20, 2013 at 14:07

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Collectives™ on Stack Overflow

Error with MySQL "CREATE TABLE" query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related