How can I check if a MySQL table exists with PHP?

Question

As simple in theory as it sounds I've done a fair amount of research and am having trouble figuring this out.

How can I check if a MySQL table exists and if it does do something. (I guess a simple php if/else statement could work for this)

Is there a way to do this?

This is what I have done with cwallenpoole's response:

mysql_connect("SERVER","USERNAME","PASSWORD"); mysql_select_db('DATABASE');  $val = mysql_query('select 1 from `TABLE`');  if($val !== FALSE) {    print("Exists"); }else{    print("Doesn't exist"); } 

Answer 1

// Select 1 from table_name will return false if the table does not exist. $val = mysql_query('select 1 from `table_name` LIMIT 1');  if($val !== FALSE) {    //DO SOMETHING! IT EXISTS! } else {     //I can't find it... } 

Admittedly, it is more Pythonic than of the PHP idiom, but on the other hand, you don't have to worry about dealing with a copious amount of extra data.

Edit

So, this answer has been marked down at least twice as of the time I am writing this message. Assuming that I had made some gargantuan error, I went and I ran some benchmarks, and this is what I found that my solution is over 10% faster than the nearest alternative when the table does not exist, and it over 25% faster when the table does exist:

:::::::::::::::::::::::::BEGINNING NON-EXISTING TABLE:::::::::::::::::::::::::::::: 23.35501408577 for bad select 25.408507823944 for select from schema num rows -- calls mysql_num_rows on select... from information_schema. 25.336688995361 for select from schema fetch row -- calls mysql_fetch_row on select... from information_schema result 50.669058799744 for SHOW TABLES FROM test :::::::::::::::::::::::::BEGINNING EXISTING TABLE:::::::::::::::::::::::::::::: 15.293519973755 for good select 20.784908056259 for select from schema num rows 21.038464069366 for select from schema fetch row 50.400309085846 for SHOW TABLES FROM test 

I tried running this against DESC, but I had a timeout after 276 seconds (24 seconds for my answer, 276 to fail to complete the description of a non existing table).

For good measure, I am benchmarking against a schema with only four tables in it and this is an almost fresh MySQL install (this is the only database so far). To see the export, look here.

AND FURTHERMORE

This particular solution is also more database independent as the same query will work in PgSQL and Oracle.

FINALLY

mysql_query() returns FALSE for errors that aren't "this table doesn't exist".

If you need to guarantee that the table doesn't exist, use mysql_errno() to get the error code and compare it to the relevant MySQL errors.

Answer 2

The cleanest way to achieve this in PHP is to simply use DESCRIBE statement.

if ( mysql_query( "DESCRIBE `my_table`" ) ) {     // my_table exists } 

I'm not sure why others are posting complicated queries for a such a straight forward problem.

Update

Using PDO

// assuming you have already setup $pdo $sh = $pdo->prepare( "DESCRIBE `my_table`"); if ( $sh->execute() ) {     // my_table exists } else {     // my_table does not exist     }