Equivalent of mysql_list_tables in PHP PDO?

Question

When using PHP PDO to access the database, is there a way to list all the tables in a database?

Something like mysql_list_tables() is whats needed.

Answer 1

Hi,

What about using a SQL query that does something like this :

show tables

Or, if needed, specifying the database :

show tables from crawler

And, if you only want to get some tables :

show tables from crawler like 'site%'

Actually, even if the mysql_list_tables() function exists, its manual page states :

This function is deprecated. It is preferable to use mysql_query() to issue a SQL SHOW TABLES [FROM db_name] [LIKE 'pattern'] statement instead.

So, this should be quite OK with PDO, I'm guessing.

And, testing with PDO :

$db = new PDO('mysql:dbname=crawler;host=127.0.0.1', 'crawler', 'crawler');
$result = $db->query("show tables");
while ($row = $result->fetch(PDO::FETCH_NUM)) {
    var_dump($row[0]);
}

I'm getting this kind of output :

string 'headers' (length=7)
string 'headers_sites' (length=13)
string 'headers_sites_0' (length=15)
...
string 'headers_sites_7' (length=15)
string 'reporting_sites_servers_software' (length=32)
string 'servers' (length=7)
string 'sites' (length=5)
string 'sites_0' (length=7)
...
string 'sites_servers' (length=13)
string 'sites_software' (length=14)
string 'software' (length=8)

Which fits with the tables I actually have in this database.

Answer 2

If you want to do this in a cross-platform way, look into Zend Framework's Zend_Db, which provides the listTables() method

Answer 3

If you want a portable way to query for the schema, you can use the ansi standard INFORMATION_SCHEMA