CODEWITHSUNDEEP
phpmysqlmysqli
user448038
user448038

Reputation:

How to get a list of databases?

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?

Upvotes: 8

Views: 26337

Answers (5)

Erwin Maas
Erwin Maas

Reputation: 1

Tried several examples from above, but couldn't get it to work, errors like array to string conversion etc... So I made this function and it works perfectly for me (the function connect() on line 3 connects to information_schema. The preg_match at the bottom filters out the standard databases (I am not so familiar with preg_match, maybe I could have done it in another way better).

function showDatabases () {
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = connect();
    $resultarray = array(); 
    $dbquery = "SELECT * FROM `SCHEMATA`";

    $stmt = mysqli_stmt_init($mysqli);
    mysqli_stmt_prepare($stmt, $dbquery);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    foreach($result as $datakey => $datavalue) {
        array_push($resultarray, $datavalue);
    }
    echo "<br><br>";
    $arraycount = count($resultarray);
    echo "Number of entries: $arraycount<br><br>";
    
    for ($x=0;$x<$arraycount;$x++) {
        if(!preg_match('/schema/', $resultarray[$x]['SCHEMA_NAME']) AND !preg_match('/myadmin/', $resultarray[$x]['SCHEMA_NAME'])) {
            print_r($resultarray[$x]['SCHEMA_NAME']);
            echo "<br>";
        }
    }
    echo "<br><br>";
    close();
}

Upvotes: -2

Aylian Craspa
Aylian Craspa

Reputation: 466

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;
    }
}

Upvotes: 0

Mark Elliot
Mark Elliot

Reputation: 77034

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

Upvotes: 13

Boycott A.I.
Boycott A.I.

Reputation: 18871

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

Upvotes: 2

Rick
Rick

Reputation: 101

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";
        }
    }

Upvotes: 7

Related Questions