MySQLi Table Exists

Question

In PHP, what would be the best way of seeing if a table exists?

This is what I am using so far

public function TableExists($table) {
    $res = $this->Query("SELECT 1 FROM $table");

    if(isset($res->num_rows)) {
        return $res->num_rows > 0 ? true : false;
    } else return false;
}

I would `SHOW TABLES` and search the results for your table name. The above is going to throw an error. — Pekka, Mar 10 '13 at 12:43

score 15 · Accepted Answer · answered Mar 10 '13 at 12:44

15

What you posted is going to throw an error if the table doesn't exist. Try this instead:

SHOW TABLES LIKE 'tablename';

And ensure that you get exactly one row back.

answered Mar 10 '13 at 12:44

Colin M

13,010
3
38
58

Alright thanks. That works great. Thanks everyone else for the answers – Ben Mar 10 '13 at 14:19
well this didnt work for me it still echo does not exist in mysqli_query() – kinsley kajiva Nov 28 '16 at 02:40
What if the table exists but it doesn't have any rows? Your approach doesn't work in this case – LoMaPh Jul 15 '18 at 05:27
This query is not impacted by the number of rows in the table. It will return a result whether the table has rows or not. – Colin M Jul 15 '18 at 11:28

score 12 · Answer 2 · answered Mar 10 '13 at 12:51

12

Colin has the right solution -- to use SHOW TABLES LIKE. Here is what it would look like using your code:

public function TableExists($table) {
  $res = $this->Query("SHOW TABLES LIKE $table");
  return mysql_num_rows($res) > 0;
}

answered Mar 10 '13 at 12:51

joshplien

336
1
7

score 2 · Answer 3 · answered Mar 10 '13 at 13:09

2

For seeing, if [table name] exist

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

answered Mar 10 '13 at 13:09

Amir

4,089
4
16
28

score 1 · Answer 4 · answered Mar 10 '13 at 13:11

1

An alternative to the SHOW TABLES approach from other answers is using the INFORMATION_SCHEMA like this:

SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'tablename';

answered Mar 10 '13 at 13:11

johannes

15,807
3
44
57

Attaullah Khan · Answer 5 · 2019-04-27T09:25:38.523

1

$con = mysqli_connect($hostname,$username,$password,$database);

if(mysqli_num_rows(mysqli_query($con,"SHOW TABLES LIKE 'accman'"))) {
  echo "DB EXIST";
} else {
  echo "DB Not Exist";
}

edited Apr 27 '19 at 09:25

answered Apr 27 '19 at 09:17

Attaullah Khan

303
2
8

MySQLi Table Exists

5 Answers5

Linked

Related