list all tables in a database with MySQLi

Question

I have looked around and still can't find how to list all my tables in a database. is it possible with MySQLi?

Thanks.

Solution 1

There are many ways.

SHOW TABLES

Is the most simple SQL statement for doing that. You can also take a look at INFORMATION_SCHEMA.TABLES if you want to have more details or do some filtering or such.

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

Solution 2

Using PHP 5.5 or later, a simple solution is using PHP's built-in array_column() function.

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

Solution 3

I'd 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;
}