List all MySQL databases

Using a PHP script or code to list all available MySQL databases in your account is extremely easy. This article will quickly show you how to get that done.

Why would I want to do that? I hear you think...

Well, I use it a lot in my administration-type scripts to manage (view, back up, restore, optimize, check and repair) all my MySQL databases and tables periodically from just one page!

What info do I need to list ALL my databases on a PHP Script?

The first thing you need is the username and password for your web hosting account. I am not talking about the individual database user and password (you may have already setup for each) here... I am talking about the main username and password that you normally submit to gain access to your virtual / shared hosting webserver!

If you use a control panel script like the ever-popular CPanel, then the username and password you would use to access your CPanel, webserver, ftp and other such restricted information is the username and password we're going to be using in our PHP script below.

Sample PHP code to list all available MySQL databases

For the purpose of this tutorial, let's assume your username is pcxgamers, your password is g5h2t3y4 and the PHP script to list all your available MySQL databases is filenamed list_mysql_dbs.php.

php:


<?php

// FILENAME: LIST_MYSQL_DBS.PHP
// ----------------------------

define( 'NL', "\n" );
define( 'TB', '  ' );

// connecting to MySQL.
$conn = @mysql_connect( 'localhost', 'pcxgamers', 'g5h2t3y4' )
        or die( mysql_errno().': '.mysql_error().NL );

// attempt to get a list of MySQL databases
// already set up in my account. This is done
// using the PHP function: mysql_list_dbs()
$result = mysql_list_dbs( $conn );

// Output the list
echo '<ul>'.NL;
 
  ///* USING: mysql_fetch_object()
  //  ---------------------------
  while( $row = mysql_fetch_object( $result ) ):
    echo TB.'<li>'.$row->Database.'</li>'.NL;
  endwhile;
  //*/

  /* USING: mysql_fetch_row()
  // ------------------------
  while( $row = mysql_fetch_row( $result ) ):
    echo TB.'<li>'.$row[0].'</li>'.NL;
  endwhile;
  //*/

  /* USING: mysql_fetch_assoc()
  // --------------------------
  while( $row = mysql_fetch_assoc( $result ) ):
    echo TB.'<li>'.$row['Database'].'</li>'.NL;
  endwhile;
  //*/

echo '</ul>'.NL;

// Free resources / close MySQL Connection
mysql_free_result( $result );
mysql_close( $conn );    

?>


The code above should result in something like this:

  • somedb_1
  • somedb_2
  • somedb_3
  • somedb_4
  • otherdb_1

Questions? Visit our PHP and MySQL Forums.

Special thanks: www.pcxgamers.com.

TOP

Do you find it easy to navigate through this site?

 Yes

 No

 Where am I?