How to check if a table exists in MySQL using PHP PDO?

Question

if($count <= 0 ) // IF TABLE DOES NOT EXIST -> CREATE AND INSERT DATA
{
    $CREATE_TABLE= "CREATE TABLE $TABLE_NAME LIKE student; INSERT $TABLE_NAME SELECT * FROM student;";
    $created = $connect->exec($CREATE_TABLE);

    if($created!=FALSE)
    {
        $SQL = "INSERT INTO $TABLE_NAME (name, roll_number, father_name, dob, gender, address, email, phone, department, program, semester, section) VALUES(:name, :roll_number, :father_name, :dob, :gender, :address, :email, :phone, :department, :program, :semester, :section)";
        $pdo_statement = $connect->prepare($SQL);

        $pdo_statement->bindparam(':name',          $name);
        $pdo_statement->bindparam(':roll_number',   $roll_number);
        $pdo_statement->bindparam(':father_name',   $father_name);
        $pdo_statement->bindparam(':dob',           $dob);
        $pdo_statement->bindparam(':gender',        $gender);
        $pdo_statement->bindparam(':address',       $address);
        $pdo_statement->bindparam(':email',         $email);
        $pdo_statement->bindparam(':phone',         $phone);
        $pdo_statement->bindparam(':department',    $department);
        $pdo_statement->bindparam(':program',       $program);
        $pdo_statement->bindparam(':semester',      $semester);
        $pdo_statement->bindparam(':section',       $section);

        $result = $pdo_statement->execute();
    }
}
else if($count > 0) // IF TABLE EXIST -> INSERT DATA
{
    $SQL = "INSERT INTO $TABLE_NAME (name, roll_number, father_name, dob, gender, address, email, phone, department, program, semester, section) VALUES (:name, :roll_number, :father_name, :dob, :gender, :address, :email, :phone, :department, :program, :semester, :section)";
    $pdo_statement = $connect->prepare($SQL);

    $pdo_statement->bindparam(':name',          $name);
    $pdo_statement->bindparam(':roll_number',   $roll_number);
    $pdo_statement->bindparam(':father_name',   $father_name);
    $pdo_statement->bindparam(':dob',           $dob);
    $pdo_statement->bindparam(':gender',        $gender);
    $pdo_statement->bindparam(':address',       $address);
    $pdo_statement->bindparam(':email',         $email);
    $pdo_statement->bindparam(':phone',         $phone);
    $pdo_statement->bindparam(':department',    $department);
    $pdo_statement->bindparam(':program',       $program);
    $pdo_statement->bindparam(':semester',      $semester);
    $pdo_statement->bindparam(':section',       $section);

    $result = $pdo_statement->execute();

} // ELSE IF ENDS

Check: https://stackoverflow.com/questions/37597538/mysql-insert-into-table-if-exists — Alexey Chuhrov, Feb 22 '18 at 11:48
Best would be to qeury information_schema.TABLES table. https://dev.mysql.com/doc/refman/5.7/en/tables-table.html — Raymond Nijland, Feb 22 '18 at 11:48
You should post the code before the `if` statement as it seems you are checking there if the table exists... — jeroen, Feb 22 '18 at 11:53

score 0 · Accepted Answer · answered Feb 22 '18 at 12:22

So i understand you will create the table, if it does not exist and insert data. So call first

$pdo->query("CREATE TABLE $TABLE IF NOT EXISTS;");

It will do nothing, when table exists.

And then insert your data.

$pdo->query("INSERT INTO $TABLE ... ");

No 'if then else' in PHP!

score -1 · Answer 2 · answered Feb 22 '18 at 12:02

function tableExists($pdo, $table) {

// Try a select statement against the table
// Run it in try/catch in case PDO is in ERRMODE_EXCEPTION.
try {
    $result = $pdo->query("SELECT 1 FROM $table LIMIT 1");
} catch (Exception $e) {
    // We got an exception == table not found
    return FALSE;
}

// Result is either boolean FALSE (no table found) or PDOStatement Object (table found)
return $result !== FALSE;

}

How to check if a table exists in MySQL using PHP PDO?

2 Answers2