Check if a database table exists using PHP/PDO

Question

I want to check if a table with a specific name exists in a database I've connected to using PHP and PDO.

It has to work on all database backends, like MySQL, SQLite, etc.

Answer 1

Here's a complete function for checking if a table exists.

/**
 * Check if a table exists in the current database.
 *
 * @param PDO $pdo PDO instance connected to a database.
 * @param string $table Table to search for.
 * @return bool TRUE if table exists, FALSE if no table found.
 */
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;
}

Note: PDO will only throw exceptions if it is told to, by default it is silent and throws no exceptions. Thats why we need to check the result as well. See PDO error handling at php.net

Answer 2

Before I go on, I do realise this is a MySQL-specific solution.

While all the solutions mentioned here may work, I (personally) like to keep PDO from throwing exceptions (personal preference, that's all).

As such, I use the following to test for table creation instead:

SHOW TABLES LIKE 'some_table_of_mine';

There's no error state generated if the table doesn't exist, you simply get a zero resultset. Works fast and consistently for me.

Answer 3

Do:

select 1 from your_table

and then catch the error. If you don't get any error, but resultset with one column containing "1", then the table exists.

Answer 4

Once you have your database handle via PDO, you can do this:

$tableExists = gettype($dbh->exec("SELECT count(*) FROM $table")) == 'integer';

Or wrap it in a function.

I tried messing around with try/catch at first, but even if the table did Not exist, there was no exception. Finally ended up with checking for the data type of the returned value from the dbh exec call. It's either an integer, if there is a match on the select count (even if there count is 0, or a boolean of false if there were no results.

I think this should work with all the database types that PDO supports, since the syntax is really simple.