Listing Tables in a Database

A few additions to the db_listdb.php script are all you need to list the tables in the databases as well. The only new function you'll see is mysql_list_tables(), which is used to list tables within a MySQL database.

The goal of this script is to list all of the databases, including the tables within those databases, on the local MySQL server.

Open a new file in your text editor and start a PHP block:
```
<?
```

Add the connection information just as you have been doing:

$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

Create a variable to hold the result of the mysql_list_dbs() function. Include the @ to suppress warnings, as well as the die() function to cause the script to end and a message to be displayed if the script can't get the list:
```
$dbs = @mysql_list_dbs($connection) or die(mysql_error());
```
You'll be looping through a result and dynamically populating a bulleted list. Start that bulleted list outside the loop:
```
$db_list = "<ul>";
```
Start a counter. You'll need it for your loop:
```
$db_num = 0;
```
Note
Use $db_num instead of $i as the counter, because at one point in this script, you'll have two counters going at the same time.
Begin a while loop. This loop will continue for as long as the value of $db_num is less than the number of rows in the $dbs result value:
```
while ($db_num < mysql_num_rows($dbs)) {
```
Once you're within the while loop, get the name of the database reflected in the current row of the result:
```
$db_names[$db_num] = mysql_tablename($dbs, $db_num);
```
Add the current database name to the bulleted list:
```
$db_list .= "<li>$db_names[$db_num]";
```
Create a variable to hold the result of the mysql_list_tables() function. Include the @ to suppress warnings, as well as the die() function to cause the script to end and a message to be displayed if the script can't get the list:
```
$tables = @mysql_list_tables($db_names[$db_num]) or die(mysql_error());
```
Note
The only argument necessary for the mysql_list_tables() function is the name of the current database.
You'll be looping through a result and dynamically populating a bulleted list. Start that bulleted list outside the loop:
```
$table_list = "<ul>";
```
Start a counter. You'll need it for your second loop:
```
$table_num = 0;
```
Begin a while loop. This loop will continue for as long as the value of $table_num is less than the number of rows in the $tables result value.
```
while ($table_num < mysql_num_rows($tables)) {
```
Once you're within the while loop, get the name of the table reflected in the current row of the result:
```
$table_names[$table_num] = mysql_tablename($tables, $table_num);
```

Add the current table name to the bulleted list:

$table_list .= "<li>$table_names[$table_num]";

Increment your count before you close the while loop:
```
$table_num++;
```
Close the inner while loop and the bulleted list of tables:
```
}
$table_list .= "</ul>";
```
Add the value of $table_list to $db_list, and then increment your count before you close the outer while loop:
```
$db_list .= "$table_list";
$db_num++;
}
```
Close the bulleted list of databases, and then your PHP block:
```
$db_list .= "</ul>";
?>
```

Add this HTML:

<HTML>
<HEAD>
<TITLE>MySQL Tables</TITLE>
</HEAD>
<BODY>
<P><strong>Databases and tables on localhost</strong>:</P>

Print the message string:
```
<? echo "$db_list"; ?>
```
Add some more HTML so that the document is valid:
```
</BODY>
</HTML>
```
Save the file with the name db_listtables.php.

Your code should look something like this:

<?
//connection code
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

//get database list
$dbs = @mysql_list_dbs($connection) or die(mysql_error());

//start first bullet list
$db_list = "<ul>";
$db_num = 0;

//loop through results of function
while ($db_num < mysql_num_rows($dbs)) {
     //get database names and make each a bullet point
$db_names[$db_num] = mysql_tablename($dbs, $db_num);
     $db_list .= "<li>$db_names[$db_num]";

     //get table names and start another bullet list
     $tables = @mysql_list_tables($db_names[$db_num]) or die(mysql_error());
     $table_list = "<ul>";
     $table_num = 0;

     //loop through results of function
     while ($table_num < mysql_num_rows($tables)) {
          //get table names and make each a bullet point
          $table_names[$table_num] = mysql_tablename($tables, $table_num);
          $table_list .= "<li>$table_names[$table_num]";
          $table_num++;
     }
     //close inner bullet list and increment number to continue loop
     $table_list .= "</ul>";
     $db_list .= "$table_list";
     $db_num++;
}
//close outer bullet list
$db_list .= "</ul>";
?>
<HTML>
<HEAD>
<TITLE>MySQL Tables</TITLE>
</HEAD>
<BODY>
<P><strong>Databases and tables on localhost</strong>:</P>
<? echo "$db_list"; ?>
</BODY>
</HTML>

It's time to see if this script lists the databases on your server, including their table. Place this file in the document root of your web server, and open your web browser to http://127.0.0.1/db_listtables.php.

Because all privileges on all tables were granted to the test user, you should see a list of all tables and databases, including those reserved by the system. Your mileage might vary, depending on your server setup and your databases and tables.

In the next section, you attempt to create new databases on your server.