Listing MySQL Tables with PHP

Listing table names does not require the removed mysql_list_tables() function. MySQL provides SHOW TABLES, and PHP can execute that statement through PDO or MySQLi.

SHOW TABLES

SHOW TABLES;

PDO example

<?php
$stmt = $dbo->query('SHOW TABLES');
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    echo htmlspecialchars($row[0], ENT_QUOTES, 'UTF-8') . '<br>';
}
?>

Filter table names with LIKE

SHOW TABLES LIKE 't%';

<?php
$stmt = $dbo->query("SHOW TABLES LIKE 't%'");
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $table) {
    echo htmlspecialchars($table, ENT_QUOTES, 'UTF-8') . '<br>';
}
?>

MySQLi example

<?php
$result = $connection->query('SHOW TABLES');
echo 'Number of tables: ' . $result->num_rows . '<br>';
while ($row = $result->fetch_row()) {
    echo htmlspecialchars($row[0], ENT_QUOTES, 'UTF-8') . '<br>';
}
?>

Historical mysql_list_tables()

<?php
// Historical only: removed from PHP 7+
$list = mysql_list_tables('sql_tutorial');
while ($i < mysql_num_rows($list)) {
    $tableName = mysql_tablename($list, $i);
    $i++;
}
?>

For portable metadata queries, you can also query INFORMATION_SCHEMA.TABLES. Related: SHOW TABLES tutorial, LIKE patterns, listing databases, and MySQLi.

← All PHP & MySQL topics

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: