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.

Answer 5

As part of your project, create a schema view.

For Oracle it would be something like

SELECT TABLE_NAME FROM ALL_TABLES

For Mysql:

SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mydbname'

ETC..

And then run a query in your code against the view.

Answer 6

You might be able to avoid having to rely on an error by using a query along the lines of "SHOW TABLES LIKE 'your_table'" and then counting the rows. I've been using this method successfully with MySQL and PDO but have yet to test it with other DBs

Answer 7

At first, I was using the accepted answer, but then I noticed it was failing with empty tables. Here is the code I'm using right now:

function DB_table_exists($db, $table){
    GLOBAL $db;
    try{
        $db->query("SELECT 1 FROM $db.$table");
    } catch (PDOException $e){
        return false;
    }
    return true;
}

This code is an extract of my extension class for PDO. It will produce an error (and return false) if the table doesn't exist, but will succeed if the table exists and/or is empty.

Answer 8

If you have other major actions to do within the same statement, you can use the e->errorInfo

try{                
    //Your major statements here
}
catch(PDOException $e){
    if($e->errorInfo[1] == 1146){
        //when table doesn't exist
    }      
}

Answer 9

This complete function is very similar to esbite's answer, but includes code to protect from SQL injection. Also, you may not get consistent results from the accepted answer when the table in question is empty.

/**
 * This function checks if the table exists in the passed PDO database connection
 * @param PDO $pdo - connection to PDO database table
 * @param type $tableName 
 * @return boolean - true if table was found, false if not
 */
function tableExists(PDO $pdo, $tableName) {
    $mrSql = "SHOW TABLES LIKE :table_name";
    $mrStmt = $pdo->prepare($mrSql);
    //protect from injection attacks
    $mrStmt->bindParam(":table_name", $tableName, PDO::PARAM_STR);

    $sqlResult = $mrStmt->execute();
    if ($sqlResult) {
        $row = $mrStmt->fetch(PDO::FETCH_NUM);
        if ($row[0]) {
            //table was found
            return true;
        } else {
            //table was not found
            return false;
        }
    } else {
        //some PDO error occurred
        echo("Could not check if table exists, Error: ".var_export($pdo->errorInfo(), true));
        return false;
    }
}

Answer 10

I recommend you to use DESCRIBE

example query:

DESCRIBE `users`

example php&pdo:

$tblname = 'users'; //table name
$x = $db->prepare("DESCRIBE `$tblname`");
$x->execute();
$row = $x->fetch();
if ($row) {
    print 1; //table exists
}else{
    print 0; //table not exists
}

Answer 11

A simple PDO two liner that works with MySQL (not sure about other DBs):

$q = $pdo->query("SHOW TABLES LIKE '{$table}'");
$tableExists = $q->fetchColumn();

Answer 12

I do a few things in my web apps with CodeIgniter to check that the database exists (and is useful), any of these can work:

@$this->load->database();
$v = @$this->db->version()
$tables = @$this->db->list_tables();

Adding the @ will suppress errors if you have them enabled in your PHP setup, and checking the results of version() and list_tables() can be used to not only determine if your DB is around (but that it's sane too).

Answer 13

Do a query where you ask the database to create a table if it doesn't exist:

$string = "CREATE TABLE IF NOT EXISTS " .$table_name . " int(11) NOT NULL AUTO_INCREMENT,
  `id` int(3) NOT NULL,
  `blabla` int(2) NOT NULL,
  `blabla1` int(2) NOT NULL,
  `blabla3` varchar(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
AUTO_INCREMENT=1 ";

$sql = $conection->prepare($string);
$sql->execute();

Answer 14

This seems to work at least with SQLite3 without exceptions, etc:

        $select =
            "SELECT CASE WHEN EXISTS (SELECT * FROM SQLITE_MASTER WHERE TYPE = 'table' AND NAME = :tableName) THEN 1 ELSE 0 END AS TABLE_EXISTS;";

Answer 15

Here's what worked for me. It was a combination of several answers:

$table_name = 'your_table_here'; 
$test = "SELECT 1 FROM " . $table_name . " LIMIT 1";
$test = $db->query($test); //$db needs to be PDO instance

if($test)
{
    return 1; //Table exists
}
else
{
    return 0; //No table in database
}