List of all tables in a database with MySQLi

Question

I looked through and still cannot find how to list all my tables in the database. Is this possible with MySQLi?

Thanks.

Answer 1

There are many ways.

SHOW TABLES 

This is the easiest SQL statement to do this. You can also take a look at INFORMATION_SCHEMA.TABLES if you want to get more detailed information or do any filtering or such.

 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'your_database'; 

Answer 2

I would try something like:

 function get_tables() { $tableList = array(); $res = mysqli_query($this->conn,"SHOW TABLES"); while($cRow = mysqli_fetch_array($res)) { $tableList[] = $cRow[0]; } return $tableList; } 

You may also be interested in removing this: https://devzone.zend.com/13/php-101-part-8-databases-and-other-animals_part-2/ (EDIT: this link refers to the mysql API, not mysqli, but most calls have a parallel mysqli).

NTN

Answer 3

Using PHP 5.5 or later, a simple solution uses the built-in PHP array_column () .

 $link = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME); $listdbtables = array_column(mysqli_fetch_all($link->query('SHOW TABLES')),0); 

Answer 4

here is a small example

 class database { public $connection; function __construct() { $this->connection = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME) or die('Database Connection Error: '.mysqli_connect_error()); } public function close_database() { return mysqli_close($this->connection); } public function query($query) { $query = mysqli_query($this->connection ,$query) or die($this->show_errors('Query Execution Error: '.mysqli_error($this->connection),'E')); return $query; } public function fetch_assoc($query) { $query = mysqli_fetch_assoc($query); return $query; } } $db = new database(); $query = $db->query("SHOW TABLES FROM DATABASENAME"); $db->fetch_assoc($query);