Listing MySQL tables with PHP

Posted April 9th, 2009 in MySql and PHP

This post shows how to use the MySQL "SHOW TABLES FROM" SQL query to get a list of tables using PHP. This list could either be stored to an array or echoed out to web browser, command line etc.

The example code below uses the raw mysql_* functions but you easily enough use a database abstraction library to achieve more or less the same thing. $server, $login, $password and $db are variables which store the obvious.

mysql_connect($server, $login, $password);
$res = mysql_query("SHOW TABLES FROM $db");
while($row = mysql_fetch_array($res, MYSQL_NUM)) {
    echo "$row[0]\n";
}

To instead put the tables into an array do the following:

mysql_connect($server, $login, $password);
$res = mysql_query("SHOW TABLES FROM $db");
$tables = array();
while($row = mysql_fetch_array($res, MYSQL_NUM)) {
    $tables[] = "$row[0]";
}

Note that the code examples above pass MYSQL_NUM as the second parameter to the mysql_fetch_array() function. This returns a numeric based array which is easier to work with; an associative array with column names will label the column "Tables_in_$db" where $db is the database name and is a little annoying to work with when doing this sort of thing.