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:

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:

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:

Hopefully, you found this tutorial to be helpful!

Related post: Get the structure of a MySQL table using PHP.