getting syntax when creating table

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, Nov 16 '16 at 20:21
You cant execute 2 queries with `query`. Also `int(16)` is not possible, you'll need `bigint`. http://dev.mysql.com/doc/refman/5.7/en/integer-types.html Note the `Maximum Value`. — chris85, Nov 16 '16 at 20:21

score 4 · Answer 1 · answered Nov 16 '16 at 20:21

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 '16 at 20:21

tadman

208,517
23
234
262

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 `;` – SirParselot Nov 16 '16 at 20:23
1

@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. – chris85 Nov 16 '16 at 20:32

score 1 · Accepted Answer · answered Nov 16 '16 at 20:21

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 '16 at 20:21

Felippe Duarte

14,901
2
25
29

I missed the multi_query part when I was trying to figure that out. Thanks for pointing that out. – SirParselot Nov 16 '16 at 20:38
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. – tadman Nov 16 '16 at 22:55
Well, that depends. For most users this could be important, but in some scenarios that function would be better. – Felippe Duarte Nov 17 '16 at 11:57

getting syntax when creating table

2 Answers2