Check if MySQL table exists or not

Question

Possible Duplicate:
MySQL check if a table exists without throwing an exception

I have a dynamic mysql query builder in my project that creates select queries from different tables.
I need to check if the current processing table exists or not.
Imagine that my tables are table1, table2, and table3. My code is something like this:

<?php
for($i = 1 ; $i <= 3 ; $i++) {
   $this_table = 'table'.$i;
   $query = mysql_query("SELECT * FROM $this_table");
   // ...
}
?>

How can I do this check (Please tell me the simplest way).

I believe [this post](http://stackoverflow.com/questions/1525784/mysql-check-if-a-table-exists-without-throwing-an-exception) has the answer — bowlerae, Jan 25 '12 at 18:42
Perhaps because it was duplicated. Check the link I posted above. — bowlerae, Jan 25 '12 at 19:25

afuzzyllama · Accepted Answer · 2016-09-13T21:26:12.390

94

Updated mysqli version:

if ($result = $mysqli->query("SHOW TABLES LIKE '".$table."'")) {
    if($result->num_rows == 1) {
        echo "Table exists";
    }
}
else {
    echo "Table does not exist";
}

Original mysql version:

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

Referenced from the PHP docs.

edited Sep 13 '16 at 21:26

answered Jan 25 '12 at 18:42

afuzzyllama

6,538
5
47
64

4

note that queries to information_schema (like SHOW TABLES) for DB with lots of tables use a lot of CPU, even if files are cached. True for the latest 5.6.x. – sivann Apr 29 '15 at 11:51
2

There is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) to ensure the functionality of your project in the future. – TRiG Jun 10 '16 at 10:38
Is this working on Views table? – ßiansor Å. Ålmerol Sep 13 '16 at 15:46
1

@ßiansorÅ.Ålmerol The answer on this question help you http://stackoverflow.com/questions/2834016/how-to-get-a-list-of-mysql-views – afuzzyllama Sep 13 '16 at 15:53
@sivann so what is the better option? Select from table? – Vitor Oct 24 '16 at 01:28
@VitorGuerreiro Use another DB or if you only create and not delete tables, write a mysql-proxy script which caches the successful (table actually exists) queries and replies instantly, and forwards only queries where table does not exist in its cache (or in db). – sivann Oct 24 '16 at 07:39
@VitorGuerreiro Example for you here: http://pastebin.com/3gTm0mkF – sivann Oct 24 '16 at 07:45

score 12 · Answer 2 · edited May 23 '17 at 10:31

12

Taken from another post

$checktable = mysql_query("SHOW TABLES LIKE '$this_table'");
$table_exists = mysql_num_rows($checktable) > 0;

edited May 23 '17 at 10:31

Community

1
1

answered Jan 25 '12 at 18:43

bowlerae

924
1
14
37

Correct me if I'm wrong (I'm genuinely asking you to, I'm NOT a database expert by any means, I'm actually wondering) but isn't it possible for a table to exist but have 0 rows? Doesn't this check if it exists AND has at least one row, as opposed to testing if it exists? Will the value of `$checktable` be different if the table doesn't exist at all vs. it existing without any content? – Jimbo Jonny Feb 24 '16 at 16:25
1

@JimboJonny Since nobody has responded I'll address this - the query searches for tables (this will be running a query against the information schema - worth a google), and as such the rows it returns will _be_ tables. Therefore if the table exists, it will show as a row in the result of this query. The number of rows in the table is irrelevant here. – Luke Mar 22 '16 at 14:25

score 10 · Answer 3 · answered Jan 25 '12 at 18:55

10

$query = mysqli_query('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME IN ("table1","table2","table3") AND TABLE_SCHEMA="yourschema"');
$tablesExists = array();
while( null!==($row=mysqli_fetch_row($query)) ){
    $tablesExists[] = $row[0];
}

answered Jan 25 '12 at 18:55

Timur

6,668
1
28
37

2

You can replace `TABLE_SCHEMA="yourschema"` by `TABLE_SCHEMA=database()` – Vincent Robert Feb 25 '13 at 15:49

score 3 · Answer 4 · answered Jan 25 '12 at 18:54

3

$result = mysql_query("SHOW TABLES FROM $dbname");

while($row = mysql_fetch_row($result)) 
{
    $arr[] = $row[0];
}

if(in_array($table,$arr))
{
  echo 'Table exists';
}

answered Jan 25 '12 at 18:54

Zul

3,627
3
21
35

score 2 · Answer 5 · answered Jan 25 '12 at 18:43

2

Use this query and then check the results.

$query = 'show tables like "test1"';

answered Jan 25 '12 at 18:43

Michael Dillon

1,037
6
16

Ionel Lupu · Answer 6 · 2012-01-25T18:45:41.860

2

You can try this

$query = mysql_query("SELECT * FROM $this_table") or die (mysql_error());

or this

$query = mysql_query("SELECT * FROM $this_table") or die ("Table does not exists!");

or this

$query = mysql_query("SELECT * FROM $this_table");

if(!$query)
   echo "The ".$this_table." does not exists";

Hope it helps!

edited Jan 25 '12 at 18:45

answered Jan 25 '12 at 18:43

Ionel Lupu

2,695
6
29
53

Nice!! Works well! – incalite Jul 19 '16 at 00:19

score 0 · Answer 7 · answered Jan 25 '12 at 18:53

MySQL way:

SHOW TABLES LIKE 'pattern';

There's also a deprecated PHP function for listing all db tables, take a look at http://php.net/manual/en/function.mysql-list-tables.php

Checkout that link, there are plenty of useful insight on the comments over there.

Check if MySQL table exists or not

7 Answers7

Linked

Related