CREATE TABLE如果NOT EXISTS失败，表已经存在

I have the following code:

我有以下代码：

$db_host = 'localhost';
$db_port = '3306';
$db_username = 'root';
$db_password = 'root';
$db_primaryDatabase = 'dsl_ams';

// Connect to the database, using the predefined database variables in /assets/repository/mysql.php
$dbConnection = new mysqli($db_host, $db_username, $db_password, $db_primaryDatabase);

// If there are errors (if the no# of errors is > 1), print out the error and cancel loading the page via exit();
if (mysqli_connect_errno()) {
    printf("Could not connect to MySQL databse: %s\n", mysqli_connect_error());
    exit();
}

$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
    `ID` int(11) unsigned NOT NULL auto_increment,
    `EMAIL` varchar(255) NOT NULL default '',
    `PASSWORD` varchar(255) NOT NULL default '',
    `PERMISSION_LEVEL` tinyint(1) unsigned NOT NULL default '1',
    `APPLICATION_COMPLETED` boolean NOT NULL default '0',
    `APPLICATION_IN_PROGRESS` boolean NOT NULL default '0',
    PRIMARY KEY  (`ID`)
)";

if(!$dbConnection->query($queryCreateUsersTable)){
    echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
}

Which outputs...

哪个输出......

Table creation failed: (1050) Table 'dsl_ams.USERS' already exists

表创建失败：（1050）表'dsl_ams.USERS'已存在

What I don't understand is: isn't IF NOT EXISTS supposed to cancel the execution of the SQL query if that table already exists? In other words, if the table exists, shouldn't it exit that if statement and not echo anything out at all, and not attempt to execute the query?

我不明白的是：如果该表已经存在，是不是因为不是EXISTS应该取消执行SQL查询？换句话说，如果表存在，它不应该退出if语句而不回显任何东西，而不是尝试执行查询吗？

Just trying to find the best way to "create a table if it doesn't exist" without outputting anything to the user.

只是试图找到“创建一个表，如果它不存在”的最佳方法，而不向用户输出任何内容。

3 个解决方案

#1

Try this

尝试这个

$query = "SELECT ID FROM USERS";
$result = mysqli_query($dbConnection, $query);

if(empty($result)) {
                $query = "CREATE TABLE USERS (
                          ID int(11) AUTO_INCREMENT,
                          EMAIL varchar(255) NOT NULL,
                          PASSWORD varchar(255) NOT NULL,
                          PERMISSION_LEVEL int,
                          APPLICATION_COMPLETED int,
                          APPLICATION_IN_PROGRESS int,
                          PRIMARY KEY  (ID)
                          )";
                $result = mysqli_query($dbConnection, $query);
}

This checks to see if anything is in the table and if it returns NULL you don't have a table.

这将检查表中是否有任何内容，如果它返回NULL，则表示没有表。

Also there is no BOOLEAN datatype in mysql, you should INT and just set it to 1 or 0 when inserting into the table. You also don't need single quotes around everything, just when you are hardcoding data into the query.

在mysql中也没有BOOLEAN数据类型，你应该INT，并在插入表时将其设置为1或0。当您将数据硬编码到查询中时，您也不需要围绕所有内容使用单引号。

Like this...

喜欢这个...

$query = "INSERT INTO USERS (EMAIL, PASSWORD, PERMISSION_LEVEL, APPLICATION_COMPLETED, APPLICATION_IN_PROGRESS) VALUES ('foobar@foobar.com', 'fjsdfbsjkbgs', 0, 0, 0)";

Hope this helps.

希望这可以帮助。

#2

To avoid outputting anything, test for the table in your php before trying to create the table. For example,

为避免输出任何内容，请在尝试创建表之前测试php中的表。例如，

$querycheck='SELECT 1 FROM `USERS`';

$query_result=$dbConnection->query($querycheck);

if ($query_result !== FALSE)
{
 // table exists
} else
{
// table does not exist, create here.
}

Best wishes,

最好的祝愿，

#3

How about you only show the error if the error number is not 1050?

如果错误号码不是1050，你只能显示错误？

if(!$dbConnection->query($queryCreateUsersTable)){
  if($dbConnection->errno != 1050){
    echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
  }
}

#1