PHP function to check if a MySQL table exists

Posted June 14th, 2008 in MySql and PHP

Yesterday I posted how to check if a MySQL table exists using show tables or the MySQL information schema. Today I am posting a simple PHP function I created which you can use to test if a table exists.

The PHP function below gets passed in a tablename and an optional database name. If the database name is not passed in then it retrieves it using the MySQL function SELECT DATABASE(). It then queries the MySQL information schema to see if the table exists and then returns either true or false.

function table_exists($tablename, $database = false) {

    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }

    $res = mysql_query("
        SELECT COUNT(*) AS count 
        FROM information_schema.tables 
        WHERE table_schema = '$database' 
        AND table_name = '$tablename'
    ");

    return mysql_result($res, 0) == 1;

}

The PHP MySQL functions are used in the above example. A database connection is assumed and there is no error checking, but you can modify it to utilise whatever database library / abstraction layer you are using in your project and improve how you see fit.

To use the function you'd do something like this:

if(table_exists('my_table_name')) {
    // do something
}
else {
    // do something else
}

and if you wanted to specify the database name as well (perhaps you are needing to query if the table exists in multiple databases other than the one you are currently connected to), you'd do this:

if(table_exists('my_table_name', 'my_database_name')) {
    // do something
}
else {
    // do something else
}

Done!

Share or Bookmark

Share or Bookmark this page using the following services. You will need to have an account with the selected service in order to post links or bookmark this page.

Subscribe or Follow

Subscribe via RSS or email, or follow me on Facebook or Twitter below. The RSS icon takes you through to Feedburner where you can select the service or application to use.

Comments