php - 在 PHP 中执行 sql 查询时出错

Question

我正在尝试学习在 php 中使用 mysql。我开始尝试在 mysql 中创建一个表，并使用 mysqli 扩展。

我的代码:

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

错误是:

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

我尝试在 mysql 命令行上粘贴相同的命令，它工作正常。在 php 中使用这个有什么问题？

Answer 1

你应该一个一个地运行查询

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

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

而不是将它们全部耦合在一个语句中。

也不要使用 mysqi_multi_query()，这个异步函数不适合日常使用。

此外，在这种特殊情况下，USE 查询是多余的。数据库应该进入构造函数:

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

另外，告诉 mysqli 自己自动抛出错误，而不是手动检查每个数据库命令的结果:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

这样你会得到整洁干净的代码:

<?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";

此代码将报告表已成功创建，或发出错误，并详细说明出了什么问题。