Error creating table in MySQL using PHP

Question

I am writing a PHP script so as to create a Database and then use it to create a Table. I assume that I am using the correct MySQL syntax yet I am faing this error - Error creating table: 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 'USE website; CREATE TABLE users ( id INT(11) UNSIGNED AUTO_INCREMENT PRI' at line 2

Here is my PHP script - create_db.php

<?php

function create_database($hostname, $username, $password) {

$h = $hostname;
$u = $username;
$p = $password;

$conn = new mysqli($h, $u, $p);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "CREATE DATABASE website;
        USE website;
        CREATE TABLE users (
        id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        firstname VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        password VARCHAR(255) NOT NULL,
        hash VARCHAR(255) NOT NULL,
        forgot_password_hash VARCHAR(255) NOT NULL,
        status ENUM('1', '0') NOT NULL
        );";

if ($conn->query($sql) === TRUE) {
echo "Table users created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
}

create_database('localhost', 'root', 'root');

?>

Add `error_reporting(E_ALL);` to the top of the file, then execute serverside and it should display errors . — levi, Jan 16 '16 at 08:20
I was getting the same error as stated in the question above. — , Jan 16 '16 at 08:22

Armen · Accepted Answer · 2016-01-16T08:26:21.187

4

You have to use multi_query() instead query()

$conn->multi_query($query)

See more details about multi_query here: http://php.net/manual/en/mysqli.multi-query.php

edited Jan 16 '16 at 08:26

answered Jan 16 '16 at 08:21

Armen

4,064
2
23
40

Error creating table in MySQL using PHP

1 Answers1