Php-mysql Course

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:
SHOW TABLES
OR:
SHOW TABLES IN `database_name`
- 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:
<?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();
?>
This code will return the following result:
users
temp
online

If you want to check if a certain table exists into a MySQL database, you can use this SQL query:
SHOW TABLES IN `databasename` WHERE `Tables_in_databasename` = 'table_name'

- For example, to check if the "users" table exists in the "tests" database, we can use this code in PHP:
<?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` 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<count($tables); $i++) {
 if(!in_array($tables[$i], $tables_db)) $eror[] = 'Table '. $tables[$i]. ' Not exist in "'. $db. '"';
 }
 }
 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('<br/>', $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".
<?php
// Free WebMaster courses - https://coursesweb.net

// 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<count($tables); $i++) {
 if(!in_array($tables[$i], $tables_db)) $eror[] = 'Table '. $tables[$i]. ' Not exist in "'. $db. '"';
 }
 }
 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('<br/>', $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: <i>'. implode(', ', $tables). '</i> exists';
?>

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which attribute is used in <a> tag for the address of the link?
src href rel
<a href="http://coursesweb.net/" title="CoursesWeb.net">CoursesWeb.net</a>
Which CSS property sets the type of the text font?
font-family text-decoration font-size
h2 {
  font-family:"Calibri",sans-serif;
}
What instruction selects all the <div> tags with class="cls"?
querySelector("div.cls") getElementsByTagName("div") querySelectorAll("div.cls")
var elm_list = document.querySelectorAll("div.cls");
var nr_elms = elm_list.length;       // number of selected items
alert(nr_elms);
Indicate the function that can be used to get the sum of values in an array.
array_sum() array_diff() array_shift()
$arr =[1, 2, 3, 4);
$arr_sum = array_sum($arr);
echo $arr_sum;       // 10
Check if table exists in database

Last accessed pages

  1. Add data from form in text file in JSON format (12407)
  2. The Mastery of Love (3943)
  3. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (108215)
  4. Introduction to ActionScript 3 (2598)
  5. JavaScript Course - Free lessons (21422)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (735)
  2. querySelector and querySelectorAll (405)
  3. Read Excel file data in PHP - PhpExcelReader (359)
  4. PHP-MySQL free course, online tutorials PHP MySQL code (348)
  5. JavaScript Course - Free lessons (272)