getting syntax when creating table [duplicate]

Question

I am trying to create a mySQL table with php and I am getting a syntax error whenever I run my code. The string is

$sql = "DROP TABLE Colors;
CREATE TABLE Colors (
color VARCHAR(30) NOT NULL,
vote INT(16) NOT NULL UNSIGNED,
hex VARCHAR(10) NOT NULL,
PRIMARY KEY (color)
)";

query line is ($conn is the connection to the server)

$conn->query($sql)

The error is

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 
'CREATE TABLE Colors (
color VARCHAR(30) NOT NULL,
vote INT(16) NOT NULL UNSIGNED' at line 2

Does anyone see what I am doing wrong?

have you tried adding IF EXISTS to the end of the drop statement — Roshan Bhumbra
– Roshan Bhumbra, Commented Nov 16, 2016 at 20:21
You cant execute 2 queries with query. Also int(16) is not possible, you'll need bigint. dev.mysql.com/doc/refman/5.7/en/integer-types.html Note the Maximum Value. — chris85
– chris85, Commented Nov 16, 2016 at 20:21

tadman · Accepted Answer · 2016-11-16 20:21:02Z

4

You need to execute one statement at a time. ; is a construct used by the command-line tool as a delimiter, it's not actually used in the MySQL protocol for communicating so it's an error. The DELIMITER value can also be changed.

Split up it looks like this:

$conn->query("DROP TABLE Colors");
$conn->query("CREATE TABLE ...");

Note, I strongly encourage you to do proper error checking here and have exceptions turned on so that if a statement fails you know about it.

answered Nov 16, 2016 at 20:21

tadman

212k23 gold badges237 silver badges266 bronze badges

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

2 Comments

SirParselot Over a year ago

I do have error checking but I didn't put it in my question. Also I was under the impression that you could put multiple queries together using ;

chris85 Over a year ago

@SirParselot Usually not the case. That is usually disable to avoid the damage a SQL injection can do. We don't know what $conn is or which query() you are using.

Felippe Duarte · Accepted Answer · 2016-11-16 20:21:26Z

1

Not sure which mysql lib you are using, but in PHP you should use function multi_query in your scenario.

$sql = "DROP TABLE Colors;
        CREATE TABLE Colors (
        color VARCHAR(30) NOT NULL,
        vote INT(16) NOT NULL UNSIGNED,
        hex VARCHAR(10) NOT NULL,
        PRIMARY KEY (color)
)";

mysqli_multi_query($conn, $sql);

answered Nov 16, 2016 at 20:21

Felippe Duarte

15.2k2 gold badges27 silver badges29 bronze badges

3 Comments

SirParselot Over a year ago

I missed the multi_query part when I was trying to figure that out. Thanks for pointing that out.

tadman Over a year ago

Getting into the habit of using multi_query is really a bad thing. Each statement might produce an error and knowing which statement produced it is very important. That the function even exists seems like a serious lapse of judgement on the part of the library designers.

Felippe Duarte Over a year ago

Well, that depends. For most users this could be important, but in some scenarios that function would be better.

Collectives™ on Stack Overflow

getting syntax when creating table [duplicate]

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related