Php mysqli if table exists

Check if table exists in database

In this tutorial you can learn how to check with PHP if one, or multiple tables exist into a MySQL database.
The SQL command that returns a list with all the tables in database is this:

— This query returns the name of the tables in a database, the result is stored into an alias named Tables_in_ databasename ; databasename is the name of the database.

For example, if we have a MySQL database named «tests», with these tables: users, temp , and online ; we can use the following code in PHP to get a list with the table’s name:

 // 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 '
'. $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 example, to check if the «users» table exists in the «tests» database, we can use this code in PHP:

 // SQL query $sql = "SHOW TABLES IN `tests` WHERE `Tables_in_tests` = 'users'"; // 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 the $result contains at least one row, the table exists, otherwise, not exist if ($result->num_rows > 0) echo 'The table "users" exists'; else echo 'The table "users" Not exists'; > else echo 'Unable to check the "tests", error - '. $conn->error; $conn->close(); ?>

Function to check if one ore multiple tables exist in a database

The following function can be used in PHP to see if one, or multiple tables exists in a MySQL database.

// check if tables passed in $tables exists in $db function tableExist($conn, $db, $tables) < $eror = false; // if $tables is a string, convert it into an Array if(is_string($tables)) $tables = array($tables); $result=$conn->query("SHOW TABLES IN `$db`"); if($result !== false) < // if at least one table in result if($result->num_rows > 0) < // traverse the $result and store all tables into an array while($row = $result->fetch_assoc()) < $tables_db[] = $row['Tables_in_'.$db]; >// check if each table from $tables is in $tables_db, if not, sets error for($i=0; $i > else $eror[] = 'There is no table in "'. $db. '"'; > else $eror[] = 'Unable to check the "'. $db. '"'; // if $eror not False, output errors and returns false, otherwise, returns true if($eror !== false) < echo implode('
', $eror); return false; > else return true; >

The tableExist() function receives three arguments:
— $conn — is the object that contains the connection to MySQL.
— $db — represents the name of the database.
— $tables — can be a string with the name of a table, or an Array with multiple table names.

• Example, check if the tables: «comments», «temp», and «users» exist in database «tests».

query("SHOW TABLES IN `$db`"); if($result !== false) < // if at least one table in result if($result->num_rows > 0) < // traverse the $result and store all tables into an array while($row = $result->fetch_assoc()) < $tables_db[] = $row['Tables_in_'.$db]; >// check if each table from $tables is in $tables_db, if not, sets error for($i=0; $i > else $eror[] = 'There is no table in "'. $db. '"'; > else $eror[] = 'Unable to check the "'. $db. '"'; // if $eror not False, output errors and returns false, otherwise, returns true if($eror !== false) < echo implode('
', $eror); return false; > else return true; > $db = 'tests'; // connect to the "tests" database $conn = new mysqli('localhost', 'root', 'pass', $db); // check connection if (mysqli_connect_errno()) < exit('Connect failed: '. mysqli_connect_error()); >// array with the tables to test $tables = array('comments', 'temp', 'users'); // calls the tableExist() function if(tableExist($conn, $db, $tables)) echo 'All the tables: '. implode(', ', $tables). ' exists'; ?>

Источник

How Can I Check if a MySQL Table Exists With PHP

As a software developer or web programmer, you might need to perform different tasks with your database, such as checking the existence of a table before creating a new one.

In this tutorial, we will discuss how to check if a MySQL table exists in PHP.

Using SQL Query

The simplest way to check if a MySQL table exists is to use a SQL query in PHP.

You can use the following code to execute a SELECT statement to check if a table exists in the database:

$table = "table_name"; $query = "SELECT 1 FROM $table LIMIT 1"; $result = mysqli_query($conn, $query); if ($result) < // Table exists >else < // Table doesn't exist >

In this code, we use the mysqli_query() function to execute the SELECT statement.

If the table exists, the function will return a result set, otherwise, it will return a false value.

Using the SHOW TABLES SQL Statement

Another method to check if a MySQL table exists is to use the SHOW TABLES SQL statement.

The following code demonstrates how to check if a table exists using this method:

$table = "table_name"; $query = "SHOW TABLES LIKE '$table'"; $result = mysqli_query($conn, $query); if (mysqli_num_rows($result) == 1) < // Table exists >else < // Table doesn't exist >

In this code, we use the SHOW TABLES statement to check if the table exists. If the table exists, the mysqli_num_rows() function will return 1, otherwise, it will return 0.

Using the mysqli_query() Function

You can also use the mysqli_query() function to check if a table exists.

The following code demonstrates how to check if a table exists using this method:

$table = "table_name"; $query = "SELECT 1 FROM $table LIMIT 1"; if (mysqli_query($conn, $query)) < // Table exists >else < // Table doesn't exist >

In this code, we use the mysqli_query() function to execute the SELECT statement.

If the table exists, the function will return a true value, otherwise, it will return a false value.

Conclusion

In this tutorial, we discussed three methods for checking if a MySQL table exists in PHP. You can use the method that suits your requirements best.

The first method uses a SELECT statement to check if a table exists, the second method uses the SHOW TABLES statement, and the third method uses the mysqli_query() function.

I hope this tutorial has been helpful in showing you how to check if a MySQL table exists in PHP.

If you have any questions or comments, please feel free to leave them below.

Источник

MySQL check if a table exists without throwing an exception

What is the best way to check if a table exists in MySQL (preferably via PDO in PHP) without throwing an exception. I do not feel like parsing the results of «SHOW TABLES LIKE» et cetera. There must be some sort of boolean query?

10 Answers 10

Querying the information_schema database using prepared statement looks like the most reliable and secure solution.

$sql = "SELECT 1 FROM information_schema.tables WHERE table_schema = database() AND table_name = ?"; $stmt = $pdo->prepare($sql); $stmt->execute([$tableName]); $exists = (bool)$stmt->fetchColumn(); 

There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future.

If you’re using MySQL 5.0 and later, you could try:

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

Any results indicate the table exists.

@nickf: It also works on databases other than MySQL. This includes PostgreSQL and SQL Server as far as I can tell.

wondering if this is a security exploit you can query information from databases you aren’t connected to.

There is no security risk — Queries to the information_schema database will only show tables that the connected user has privileges to.

Using mysqli I’ve created following function. Assuming you have an mysqli instance called $con.

function table_exist($con, $table)< $table = $con->real_escape_string($table); $sql = "show tables like '".$table."'"; $res = $con->query($sql); return ($res->num_rows > 0); > 

Warning: as sugested by @jcaron this function could be vulnerable to sqlinjection attacs, so make sure your $table var is clean or even better use parameterised queries.

Only if you let someone fill the $table var, not every var inside a sql statment is dangerous, only if you get the data from untrusted sources. Of course you are responsible of how you use the function and do the filtering. there is no need to downvote this answer.

If you publish code like this, someone will end up using it in a place where the data has not been properly checked, and will end up with an SQL injection. Just use parameterised requests, and you will avoid any issue, whether the data has been checked or not. There is no reason whatsoever to not do so here, it’s just bad practice.

I beg my pardon, I confused this answer with another one. You are right, in this particular case real_escape_string could be used.

This is posted simply if anyone comes looking for this question. Even though its been answered a bit. Some of the replies make it more complex than it needed to be.

if (mysqli_num_rows( mysqli_query( $con,"SHOW TABLES LIKE '" . $table . "'") ) > 0 or die ("No table set") ) 

if ($con->query( "SHOW TABLES LIKE '" . $table . "'" )->rowCount() > 0 or die("No table set") ) 

delimiter $$ CREATE FUNCTION TABLE_EXISTS(_table_name VARCHAR(45)) RETURNS BOOLEAN DETERMINISTIC READS SQL DATA BEGIN DECLARE _exists TINYINT(1) DEFAULT 0; SELECT COUNT(*) INTO _exists FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = _table_name; RETURN _exists; END$$ SELECT TABLE_EXISTS('you_table_name') as _exists 

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

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

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