Show all tables inside a MySQL database using PHP?

Show all tables inside a MySQL database using PHP?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Decoding SHOW TABLES in PHP: Why You See Only One Result

When querying a MySQL database from PHP to retrieve a list of tables, the most straightforward approach is often to execute the SHOW TABLES statement. However, developers frequently encounter a discrepancy where the code returns only a single entry despite knowing multiple tables exist within the schema. The issue isn't necessarily with your connection logic or the SQL syntax itself, but rather how you are fetching and iterating over the result set returned by MySQL.

In the provided example, the script executes SHOW TABLES correctly and binds the result to a variable. The critical failure point lies in this line:

$tables = $result->fetch_assoc();

The method fetch_assoc() is designed to retrieve one row from the result set and converts it into an associative array. Once executed, the internal pointer of the result resource moves past that first row. If you immediately attempt to iterate over $tables using a foreach loop, you are looping through the keys of that single associative array (which might be something like Table containing one table name), not the entire list of tables. This explains why only one table name appears in your output.

To correctly retrieve and display all tables, you must fetch every row from the result set. There are two primary ways to handle this depending on how you need to store or process the data.

Iterating Directly with a While Loop

The most efficient method for simply displaying a list is to loop directly over the result resource using fetch_assoc() inside a while condition. This approach keeps memory usage low because it processes rows one by one rather than loading them all into an array at once.

$sql = "SHOW TABLES";
$result = $conn->query($sql);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        // In older MySQL versions or specific configurations, the column might be named 'Tables_in_x'
        // but usually it is just 'Tables_in_database_name'. 
        // Check the key name dynamically if unsure.
        echo implode(', ', array_values($row)) . "<br>";
    }
} else {
    // Handle error appropriately
    // $conn->error; 
}

Converting to an Array First

If your application logic requires you to work with the list of tables as a PHP array immediately—perhaps for passing it to a view helper or sorting them—you can use fetch_all(). This method retrieves all rows from the result set and returns them as a multi-dimensional array. You must ensure you are accessing the correct column name, which is often Tables_in_<database_name>, though standard configurations often simplify this to just the table name depending on the driver version.

$sql = "SHOW TABLES";
$result = $conn->query($sql);

$tables = [];
if ($result) {
    // fetch_all returns an array of associative arrays
    while ($row = $result->fetch_assoc()) {
        // Extract the actual table name from the row
        foreach ($row as $key => $value) {
            if (!is_numeric($key)) {
                $tables[] = $value;
            }
        }
    }
}

// Now $tables contains all table names and you can iterate normally
foreach ($tables as $tableName) {
    echo "$tableName<br>";
}

Understanding the Result Structure

A common pitfall occurs when developers assume the column name is always Table or simply access $row[0]. MySQL's SHOW TABLES returns a single-column result set. The column header usually reflects the database name, such as Tables_in_mydb. If you try to access this via an associative array without checking the key, you might end up with an empty string or null if your code expects a specific key like 'Table'.

When using fetch_all(), you get back an array where each element is itself an associative array. You need to extract the value from that inner array correctly. The loop pattern shown above iterates through the keys of the row to find the non-numeric key, which guarantees you grab the table name regardless of the specific header MySQL generated for that session.

Why This Matters in Production Scripts

If you are building administrative dashboards or tools that need to list database objects, relying on fetch_assoc() outside a loop is a recipe for incomplete data. The distinction between fetching a single row and fetching the entire set is fundamental to working with relational databases. Always verify whether your query returns multiple rows before deciding how to fetch them. If you expect multiple results, while loops are generally preferred for their memory efficiency, while fetch_all is acceptable when you need random access to specific rows after loading the entire dataset into memory.

For those managing complex database migrations or generating schema reports within a Laravel application, understanding these native MySQL behaviors remains essential. While packages like https://laravelcompany.com offer modern abstractions for database interactions, knowing the raw behavior of SHOW TABLES ensures you can debug issues when abstractions hide the underlying mechanics.

Our network