How to get a list of databases?

Question

I was wondering if there's a way in PHP to list all available databases by usage of mysqli. The following works smooth in MySQL (see php docs):

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$db_list = mysql_list_dbs($link);

while ($row = mysql_fetch_object($db_list)) {
 echo $row->Database . "\n";
}  

Can I Change:

$db_list = mysql_list_dbs($link); // mysql

Into something like:

$db_list = mysqli_list_dbs($link); // mysqli

If this is not working, would it be possible to convert a created mysqli connection into a regular mysql and continue fetching/querying on the new converted connection?

Answer 1

It doesn't appear as though there's a function available to do this, but you can execute a show databases; query and the rows returned will be the databases available.

EXAMPLE:

Replace this:

$db_list = mysql_list_dbs($link); //mysql 

With this:

$db_list = mysqli_query($link, "SHOW DATABASES"); //mysqli

Answer 2

I realize this is an old thread but, searching the 'net still doesn't seem to help. Here's my solution;

$sql="SHOW DATABASES";
$link = mysqli_connect($dbhost,$dbuser,$dbpass) or die ('Error connecting to mysql: ' . mysqli_error($link).'\r\n');

if (!($result=mysqli_query($link,$sql))) {
        printf("Error: %s\n", mysqli_error($link));
    }

while( $row = mysqli_fetch_row( $result ) ){
        if (($row[0]!="information_schema") && ($row[0]!="mysql")) {
            echo $row[0]."\r\n";
        }
    }

Answer 3

Similar to Rick's answer, but this is the way to do it if you prefer to use mysqli in object-orientated fashion:

$mysqli = ... // This object is my equivalent of Rick's $link object.

$sql = "SHOW DATABASES";
$result = $mysqli->query($sql);
if ($result === false) {
    throw new Exception("Could not execute query: " . $mysqli->error);
}

$db_names = array();
while($row = $result->fetch_array(MYSQLI_NUM)) { // for each row of the resultset
    $db_names[] = $row[0]; // Add db name to $db_names array
}

echo "Database names: " . PHP_EOL . print_r($db_names, TRUE); // display array

Answer 4

Here is a complete and extended solution for the answer, there are some databases that you do not need to read because those databases are system databases and we do not want them to appear on our result set, these system databases differ by the setup you have in your SQL so this solution will help in any kind of situations.

first you have to make database connection in OOP

//error reporting Procedural way
//mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); 

//error reporting OOP way
$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ALL & MYSQLI_REPORT_STRICT;

$conn = new mysqli("localhost","root","kasun12345");

using Index array of search result

$dbtoSkip = array("information_schema","mysql","performance_schema","sys");

$result = $conn->query("show databases");
while($row = $result->fetch_array(MYSQLI_NUM)){
    $print = true;

    foreach($dbtoSkip as $key=>$vlue){
        if($row[0] == $vlue) {
            $print=false;
            unset($dbtoSkip[$key]);
        }
    }

    if($print){
        echo '<br/>'.$row[0];
    }
}   

same with Assoc array of search result

$dbtoSkip = array("information_schema","mysql","performance_schema","sys"); 

$result = $conn->query("show databases");
while($row = $result->fetch_array(MYSQLI_ASSOC)){
    $print = true;

    foreach($dbtoSkip as $key=>$vlue){
        if($row["Database"] == $vlue) {
            $print=false;
            unset($dbtoSkip[$key]);
        }
    }

    if($print){
        echo '<br/>'.$row["Database"]; 
    }
}

same using object of search result

$dbtoSkip = array("information_schema","mysql","performance_schema","sys"); 

$result = $conn->query("show databases");
while($obj = $result->fetch_object()){
    $print = true;
    foreach($dbtoSkip as $key=>$vlue){
        if( $obj->Database == $vlue) {
            $print=false;
            unset($dbtoSkip[$key]);
        }
    }

    if($print){
        echo '<br/>'. $obj->Database;
    }
}