Error performing sql query in PHP [duplicate]

Question

I am trying to learn using mysql in php. I started off trying to create a table in mysql, and using the mysqli extension.

My code:

<?php
$truemsg = "Table created successfully";
$falsemsg = "Error creating table: ";

$servername = "localhost";
$username = "myuser";
$password = "mypass";
$db = "mytable";


// Create database
$sql = "USE ".$db.";".
'CREATE TABLE IF NOT EXISTS Authentication (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userid VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
role VARCHAR(20) NOT NULL,
email VARCHAR(50)
);';

print "Sql command is ".$sql;

// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
print "<p></p>";
if ($conn->query($sql) === TRUE) {
    echo $truemsg;
} else {
    echo $falsemsg . $conn->error;
}

$conn->close();
?>

The error is:

Sql command is USE mytable;CREATE TABLE IF NOT EXISTS Authentication ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, userid VARCHAR(30) NOT NULL, password VARCHAR(30) NOT NULL, role VARCHAR(20) NOT NULL, email VARCHAR(50) );
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 'CREATE TABLE IF NOT EXISTS Authentication ( id INT(6) UNSIGNED AUTO_INCREMENT PR' at line 1

I tried pasting the same command on the mysql command line, and it works fine. What's the problem using this in php?

USe mysqli_multi_query for multiple queries php.net/manual/ro/mysqli.multi-query.php OR remove USE db and just put the db in the connection — Mihai
– Mihai, Commented Sep 20, 2015 at 9:50

Your Common Sense · Accepted Answer · 2015-09-20 10:08:22Z

You are supposed to run queries one by one

$sql = "query one";
$conn->query($sql);

$sql = 'query two';
$conn->query($sql);

instead of coupling them all in one statement.

DO NOT use mysqi_multi_query() either, this asynchronous function is not intended for the everyday use.

Also, in this particular case USE query is superfluous. Database should go into constructor:

$conn = new mysqli($servername, $username, $password, $db);
                                                       ^^^ here

Also, tell mysqli to throw errors by itself, automatically, instead of checking result of every database command manually:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

This way you will get neat and clean code:

<?php
$servername = "localhost";
$username = "myuser";
$password = "mypass";
$db = "mytable";

// Create data table
$sql = 'CREATE TABLE IF NOT EXISTS Authentication (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userid VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
role VARCHAR(20) NOT NULL,
email VARCHAR(50)
)';

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $db);

// Run a query
$conn->query($sql);
echo "Table created successfully";

This code will either report that table has been created successfully, or emit an error, with a detailed explanation on what went wrong.

Master Yoda · Accepted Answer · 2015-09-20 09:56:16Z

3

This seems to be like a mysql multiple query problem

$conn->select_db($db);

you can use this function before the query to use the database and remove the use database statement from your query string , then you query string becomes

$sql = 'CREATE TABLE IF NOT EXISTS Authentication (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userid VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
role VARCHAR(20) NOT NULL,
email VARCHAR(50)
)';

that may work for you ..

answered Sep 20, 2015 at 9:56

Master Yoda

5318 silver badges23 bronze badges

Collectives™ on Stack Overflow

Error performing sql query in PHP [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related