How can we write PHP script to get the list of MySQL database?

We can write PHP scripts to get the list of available MySQL databases using different approaches. Since the legacy MySQL extension is deprecated, we'll show modern methods using MySQLi and PDO.

Using MySQLi Extension

The MySQLi extension provides a simple way to list databases −

<?php
$host = "localhost";
$username = "your_username";
$password = "your_password";

// Create connection
$con = new mysqli($host, $username, $password);

// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

// Query to get database list
$result = $con->query("SHOW DATABASES");

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row["Database"] . "<br>";
    }
} else {
    echo "No databases found.";
}

$con->close();
?>

Using PDO Extension

PDO provides a more secure and flexible approach −

<?php
$host = "localhost";
$username = "your_username";
$password = "your_password";

try {
    $pdo = new PDO("mysql:host=$host", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->query("SHOW DATABASES");
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['Database'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Comparison

Conclusion

Use MySQLi or PDO instead of the deprecated MySQL extension. PDO is recommended for its database-agnostic nature and superior security features. Always use proper error handling and secure connection parameters.