I wonder how can I get the list of MySQL databases in PHP using PDO without having to connect to a database first ( I mean no dbname in dsn )?
Usually I used to use the function mysql_list_dbs() but I no longer use mysql this way.
4 Answers
Thanks nick rulez. I made an example of DBs listing:
$user = 'root';
$pass = 'root';
$server = 'localhost';
$dbh = new PDO( "mysql:host=$server", $user, $pass );
$dbs = $dbh->query( 'SHOW DATABASES' );
while( ( $db = $dbs->fetchColumn( 0 ) ) !== false )
{
echo $db.'<br>';
}
Comments
You can use
show databases
or a query on the information_schema:
select schema_name from information_schema.schemata
2 Comments
taabouzeid
Yeah I know the query for that but what will be my dsn ? I want to do that from inside PHP
Charles
@ta, you don't actually need specify a database to connect to when establishing a connection to MySQL. Some other database engines aren't as forgiving. After connecting, you can issue a
USE command to switch to your database of choice. Also @nick, +1 for information_schematry{
$DBH = new PDO("mysql:host=localhost", "root", "");
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch(PDOException $e) {
echo "Fail";
}
$rs = $dbo->query("SHOW DATABASES");
while ($h = $rs->fetch(PDO::FETCH_NUM)) {
echo $r[0]."<br>";
}
1 Comment
Steve
Old answer, but shouldn't that be "echo $h[0]" ?
Another method similar to Falcon's:
This script uses foreach instead of while and print instead of echo for the db names and the break tag is set up as it would be used with XML. It also uses the associative property, the column name, instead of the array index of the column in the returned row to display the desired result.
This assumes you have the PDO connection properly set up and = $dbconn. Many times $db is used instead of $dbconn in examples.
$stmt ="SHOW DATABASES";
foreach($dbconn->query($stmt) as $row){
print $row['Database'];echo"<br />";
}
show databaseslike @nick rulez mentioned; however you will still need to at least log in to the database server and create the connection.