SirParselot SirParselot - 20 days ago 7
MySQL Question

getting syntax when creating table

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?

Answer

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