PHP: Get a list of all MySQL databases using PDO.

This is a short tutorial on how to get a list of all MySQL databases using PHP’s PDO object. To do this, we will connect to MySQL and execute a “SHOW DATABASES” query.

Take a look at the following snippet:

//Your MySQL server connection details.
$host = '127.0.0.1';
$port = '3306';
$user = 'root';
$password = '';

//Connect to the MySQL server using PDO.
$pdo = new PDO("mysql:host=$host;port=$port", $user, $password);

//Execute a "SHOW DATABASES" SQL query.
$stmt = $pdo->query('SHOW DATABASES');

//Fetch the columns from the returned PDOStatement
$databases = $stmt->fetchAll(PDO::FETCH_COLUMN);

//Loop through the database list and print it out.
foreach($databases as $database){
    //$database will contain the database name
    //in a string format
    echo $database, '<br>';
}

In the code above:

  1. We connected to the MySQL server using the PDO class. Note that in this case, we omitted the database argument from the $dsn string in the constructor.
  2. Using the query function, we executed a “SHOW DATABASES” SQL statement. It is worth pointing out that you can also use the query “SHOW SCHEMAS”, as they are both synonyms of each other.
  3. We fetched the list of database names by using the fetchAll function in conjunction with PDO::FETCH_COLUMN.
  4. Finally, we looped through our array of database names and printed them out.

LIKE clause.

You can also search for a specific database by using a LIKE clause. For example, if you wanted to retrieve a list of all databases starting with “users_”, then you could do something like this:

//Using a LIKE clause w/ a wildcard.
$stmt = $pdo->query("SHOW DATABASES LIKE 'users_%'");

$databases = $stmt->fetchAll(PDO::FETCH_COLUMN);

var_dump($databases);

In the query above, I used the LIKE clause with a wildcard operator.

Hopefully, you found this guide useful!

Related: Get a list of tables.