CREATE TABLE IF NOT EXISTS fails with table already exists

Question

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

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?

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

Answer 1

Community warning: the approach below will cause your code to FAIL in case the table doesn't exist. You should use either a regular CREATE TABLE IF NOT EXISTS query, using correct spelling and letter case for the table name, or, in case it doesn't work, refer to the question pinned at the top of this post.

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.

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.

Like this...

$query = "INSERT INTO USERS (EMAIL, PASSWORD, PERMISSION_LEVEL, APPLICATION_COMPLETED, APPLICATION_IN_PROGRESS) VALUES ('[email protected]', 'fjsdfbsjkbgs', 0, 0, 0)";