Find if table exists

blackbox

Hi all,

I need to find out if a table exists before I create it or perform an insert.

I have spent the last few hours trying to fing a solution, read many posts but fail to get anything to work.

This is what I have so far:

function mysql_table_exists($TableQ){

$exists = mysql_query("SELECT 1 FROM ".$TableQ." LIMIT 0");

if ($exists) return true;

else

return false;

}
if (!mysql_table_exists($TableQ)) {
	// create tyable
} else {
	// perform insert
}

Am I doing this right or should I be looking at another way to get it working.

Many thanks in advance.

laserlight

You can use CREATE TABLE IF NOT EXISTS to create the table. For insertions: attempt to insert then handle the error by creating the table, then attempt the insertion again.

If you really want, I suppose you could use the fact that MySQL stores data about the database schema in metadata tables (INFORMATION_SCHEMA database), but doing it that way would mean that you're always running two SQL statements where you only need one.

By the way, remember to use the MySQLi extension or PDO extension rather than the deprecated MySQL extension.

sneakyimp

this code will connect to a database and get you an array containing all the tables in that database:

$db = new mysqli('localhost', 'username', 'password', 'database_name');

if ($db->connect_error) {
	die('Connect Error (' . $db->connect_errno . ') '
			. $db->connect_error);
}

if (!$query = $db->query("SHOW TABLES")) {
	die("query is false, query failed");
}

$tables = array();
while($row = $query->fetch_row()){
	$tables[] = $row[0];
}

$db->close();

Once you you have your $tables array, you can check for the existence of a particular table like so:

if (in_array("some_table", $tables)) {
  echo "table exists!";
} else {
  echo "table does not exist";
}

barand

If you are having to create tables on the fly then your database is badly designed.

laserlight

There may be valid scenarios in which such an operation is required without it being due to poor design, e.g., as part of an upgrade script.

Weedpacket

Another possibility is a migration script - so I'll throw out there that MySQL does provide the standard SQL INFORMATION_SCHEMA interface to its system catalogues, so you can write [font=monospace]SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='db'[/font] to get a list of tables in database db; more convenient if different DBMSs are involved since the same query can be used on both.