Checking that a table exists on MySQL

Question

I am trying to check whether a table exists, and if so then do some actions. I keep on getting an error telling me that the table does not exist rather than completing my check. Here is the code:

$tableExists = $db->prepare("SHOW TABLES LIKE $table_array");
$tableExists->execute();
if($tableExists->rowCount() > 0) {
   // do some code
 } else {
   echo "Unable to add because table does not exists";
}

UPDATE: Per suggestions below, I now do the following:

$tableExists = $db->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"); 
$tableExists->execute(array($table_array)); 
if(!is_null($tableExist)) { 
    //do something
} else {
    echo "table does not exist;
}

However, the if statement does not seem to work to determine whether the table exists or not. What else could I do?

Hydra IO · Accepted Answer · 2014-08-14T17:41:23.290

11

Try using the information_schema to ask if the table exists. Something like

SELECT 
  * 
FROM
  information_schema 
WHERE TABLE_NAME = "$table_array"

Take a look through everything the information_schema holds, you will be pleasantly surprised by the information it has stored about your databases :)

edited Aug 14 '14 at 17:41

answered Aug 05 '13 at 22:17

Hydra IO

1,537
1
13
28

2

That... Is... Awesome... And to say that I took that table as part of the phpMyAdmin intrusive tables... :) – Salketer Aug 05 '13 at 22:20
My name is Alfred Salketer Arengard (Yeh, I know...), and I "aprove" this answer... @jaczes ;) – Salketer Aug 05 '13 at 22:45
OK, that works, but how do I test if it is empty? This is what I have: $tableExists = $db->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"); $tableExists->execute(array($table_array)); if(!is_null($tableExist)) { – Danconia Aug 05 '13 at 22:52
You can do `Select COUNT(*) from ...` -> 1 and more = there is table, 0 = no table – jaczes Aug 05 '13 at 22:56
Sorry, I think I didn't explain myself correctly. How do I check if the result for the query to check if the table exists is positive or not? – Danconia Aug 05 '13 at 22:58
What is returned by your query when the table does not exists so that I can check for that? - @jaczes – Danconia Aug 06 '13 at 00:10
i didnt reply because im bad in PHP... 1(or more)= there is table, 0 = no table – jaczes Aug 06 '13 at 00:13
got it to work, just changed the if to '($getfields->rowCount() == 0) – Danconia Aug 06 '13 at 00:19

score 2 · Answer 2 · edited May 23 '17 at 11:58

2

if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$table."'"))==1) 
    echo "Table exists";
else echo "Table does not exist";

ref: check if MySQL table exists or not

edited May 23 '17 at 11:58

Community

1
1

answered Aug 05 '13 at 22:15

Salketer

14,263
2
30
58

Thanks, I searched and had not found that other answer – Danconia Aug 05 '13 at 22:17
do you know how I can check whether the table exists given the update above? – Danconia Aug 05 '13 at 23:44

score 2 · Answer 3 · edited May 26 '20 at 21:18

2

Try this:

select case when (select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='offices') = 1 then 'exists' else 'does not exist' end

edited May 26 '20 at 21:18

TRiG

10,148
7
57
107

answered Aug 05 '13 at 22:24

Brett Poole

31
4

Hi, could you explain that again? This is what I have now, I just don't know how to test whether the result for the query is valid (table exists) or not. $tableExists = $db->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"); $tableExists->execute(array($table_array)); if(!is_null($tableExist)) {//do code} – Danconia Aug 05 '13 at 23:13

score -2 · Answer 4 · edited Mar 22 '16 at 14:05

-2

Try this:

select * from table_schema //this is your database name    
where table_name // your table name    
= '$table_array'

Hope this works for you.

edited Mar 22 '16 at 14:05

Nagama Inamdar

2,851
22
39
48

answered Mar 22 '16 at 13:27

snakeydoc

1

Checking that a table exists on MySQL

4 Answers4