Listing Databases on a Server

Now that you've successfully used PHP to make a connection to MySQL, it's time to familiarize yourself with some of the built-in MySQL-related functions. In this section, you use the following functions:

mysql_list_dbs()— Used to list the databases on a MySQL server.
mysql_num_rows()— Returns the number of rows in a result set.
mysql_tablename()— Despite its name, can extract the name of a table or a database from a result.

The goal of this script is to list all the databases on the local MySQL server.

Open a new file in your text editor and start a PHP block:
```
<?
```
Create a variable to hold the result of the mysql_connect() 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 connection fails:
```
$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());
```
Note
The only argument necessary for the mysql_list_dbs() function is the link identifier for the current connection.
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:
```
$i = 0;
```
Begin a while loop. This loop will continue for as long as the value of $i is less than the number of rows in the $dbs result value:
```
while ($i < 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[$i] = mysql_tablename($dbs, $i);

Note

The variable $i is replaced by its value, so during the first loop, this line would be something like

$db_names[0] = mysql_tablename($dbs, 0);

Counting starts at 0, not 1, so this would reflect the first row in the result. As the counter is incremented, so is the row number.

Add the current database name to the bullet list:
```
$db_list .= "<li>$db_names[$i]";
```
Increment your count before you close the while loop:
```
$i++;
```
Close the while loop, the bulleted list, and your PHP block:
```
}
$db_list .= "</ul>";
?>
```

Add this HTML:

<HTML>
<HEAD>
<TITLE>MySQL Databases</TITLE>
</HEAD>
<BODY>
<P><strong>Databases 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_listdb.php.

All together, your code should look like this:

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

$dbs = @mysql_list_dbs($connection)or die(mysql_error());
$db_list ="<ul>";
$i =0;

while ($i < mysql_num_rows($dbs)){
     $db_names[$i] = mysql_tablename($dbs,$i);
     $db_list .= "<li>$db_names[$i]";
     $i++;
}
$db_list .="</ul>";
?>
<HTML>
<HEAD>
<TITLE>MySQL Databases</TITLE>
</HEAD>
<BODY>
<P><strong>Databases on localhost</strong>:</P>
<? echo "$db_list"; ?>
</BODY>
</HTML>

Place this file in the document root of your web server, and then open your Web browser and type http://127.0.0.1/db_listdb.php.

Your list might vary, depending on how much you played around with things in the first chapter, but you should at least see the MySQL system database (mysql) and the database created in Chapter 1 (testDB). Next, you add another loop to this script to print the tables within each database.