MySQLi how to check if table exists?

Question

I want to create table by app if there's no such table. But doing it for the first time... Need some help, tho

 //connecting...
$mysqli = new mysqli($db_params['host'], $db_params['login'],   $db_params['pass'], $db_params['name']);

if ($mysqli->query("SHOW TABLES LIKE `products`")){
echo ' YES';
} else echo 'no';

It always says NO.

@Albzi just missed it while typing my question, thx. In real code i have it) — Andrew Nevskiy, Jun 24 '16 at 11:59
Try adding `echo $mysqli->error;` and see what MySQL is saying — Machavity, Jun 24 '16 at 12:02
You must have a string to the right of `LIKE` e.g. `SHOW TABLES LIKE 'products'`. In general try your queries in the command-line before writing the corresponding PHP code. — apokryfos, Jun 24 '16 at 12:04
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`products`' at line 1 — Andrew Nevskiy, Jun 24 '16 at 12:05
The MySQL user account connecting might not have permission to execute show tables — Jeff Puckett, Jun 24 '16 at 12:12

Erik van de Ven · Answer 1 · 2016-06-24T12:43:56.860

Read their documentation? https://dev.mysql.com/doc/refman/5.5/en/replication-features-create-if-not-exists.html Seems like you can do that easily:

CREATE TABLE IF NOT EXISTS `products`

This way you don't have to check first whether a table exists or not, you just create one if it doesn't.

And it seems like you have a syntax error, which is probably the reason why your code keeps returning "no". This should work:

SHOW TABLES LIKE 'products';

Just use single or double quotes, no backticks like `.

You use backticks (`) for table and column names, single (') or double quotes (") for strings, in this case you are giving a string so you should use single or double quotes.

score 2 · Answer 2 · answered Jun 24 '16 at 12:45

2

Use PHP DESCRIBE statement.

if(mysql_query("DESCRIBE `table_name`")) {
    // Exists
}

answered Jun 24 '16 at 12:45

Hiren Makwana

1,976
2
13
28

This is `mysql_*`. You should be `mysqli_*` or PDO – Editor Sep 13 '16 at 11:59

score 1 · Answer 3 · answered Jun 24 '16 at 12:07

1

In order to create a table if it not exists, you can use

CREATE TABLE IF NOT EXISTS

answered Jun 24 '16 at 12:07

jophab

5,356
14
41
60

Soufiane ROCHDI · Answer 4 · 2017-12-25T00:38:42.973

-1

This solution works for me JUST FINE:

<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', 'pass', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// SQL query
$sql = "SHOW TABLES IN `tests`";

// perform the query and store the result
$result = $conn->query($sql);

// if the $result not False, and contains at least one row
if($result !== false) {
  // if at least one table in result
  if($result->num_rows > 0) {
    // traverse the $result and output the name of the table(s)
    while($row = $result->fetch_assoc()) {
      echo '<br />'. $row['Tables_in_tests'];
    }
  }
  else echo 'There is no table in "tests"';
}
else echo 'Unable to check the "tests", error - '. $conn->error;

$conn->close();
?>

For a complete and more examples, here the source : http://coursesweb.net/php-mysql/check-table-exists-database_t

edited Dec 25 '17 at 00:38

answered Dec 24 '17 at 23:04

Soufiane ROCHDI

1,543
17
24

1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/18345381) – Charlie Fish Dec 25 '17 at 00:08
@CharlieFish, here is the code, but why the -1 ;) – Soufiane ROCHDI Dec 25 '17 at 00:39
I didn't downvote you. – Charlie Fish Dec 25 '17 at 00:40
I think you presented a very bad solution, you have to go through all the tables it's not a clean code that's why people rated you poorly. – Don Globian Aug 22 '23 at 12:08

MySQLi how to check if table exists?

4 Answers4