PHP: Select list of MySQL tables (PDO).

This is a tutorial on how to retrieve a list of MySQL tables using PHP. In this example, I will be using the PDO object, simply because the older mysql_* functions are no longer supported.

Have a look at the following example:

<?php

//Connect to MySQL using the PDO object.
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

//Our SQL statement, which will select a list of tables from the current MySQL database.
$sql = "SHOW TABLES";

//Prepare our SQL statement,
$statement = $pdo->prepare($sql);

//Execute the statement.
$statement->execute();

//Fetch the rows from our statement.
$tables = $statement->fetchAll(PDO::FETCH_NUM);

//Loop through our table names.
foreach($tables as $table){
    //Print the table name out onto the page.
    echo $table[0], '<br>';
}

An explanation of the code above:

  1. We connect to MySQL using the PDO object. In this particular example, I am using the database “test”.
  2. We create our SQL statement, which is “SHOW TABLES”. This SQL statement tells MySQL to return a list of the tables that exist in our currently-selected database.
  3. We prepare the SQL statement.
  4. We execute the SQL statement.
  5. We fetch the results using fetchAll.
  6. Finally, we loop through the results and print out the name of each table.

Note: If you use PDO:FETCH_ASSOC instead of PDO::FETCH_NUM, then you will have to access the table name via the associative key “Tables_in_test” (or “Tables_in_[your chosen database name here]“).

To select a list of tables from a different MySQL database, then you can use the following SQL statement:

<?php

//Selecting a list of table names from a different database.
$sql = "SHOW TABLES FROM my_other_database";

Note that if the specified database does not exist, then an empty result set will be returned. i.e. No errors are thrown (this can lead to bugs if you’re not careful).

To return a list of MySQL tables that have a certain name, then you can use something like this:

<?php

//For this example, we want a list of tables
//that contain the word "user" in their name.

$sql = "SHOW TABLES FROM my_database_name LIKE '%user%';"

Hopefully, you found this tutorial to be helpful!

Related posts: