Asked 7 years ago
30 Jan 2017
Views 2177
sqltreat

sqltreat posted

alternative of mysql_list_dbs in php

i am finding alternative of the mysql_list_dbs in php ,
in php , it was easy to get list of database by function mysql_list_dbs

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


above code will give you list of databases in current MySQL database.

but mysql_list_dbs is deprecated as of PHP 5.4.0 so what is the other alternative of mysql_list_dbs function ? is there any PHP function or MySQL function which is used to get database list
Mitul Dabhi

Mitul Dabhi
answered Nov 30 '-1 00:00

MySQL query : SHOW DATABASES
which return all database as result

as you use mysqli to get table 's data , in same manor you can use for SHOW DATABASES


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

if ($result = $mysqli->query("SHOW DATABASES")) {

        if($result){
                     // Cycle through results
                    while ($row = $result->fetch_object()){
                echo $row ."\n";
            }
            // Free result set
            $result->close();
}

$mysqli->close();
?>



Above code will result database list

or if you using older version of php than


$res = mysql_query("SHOW DATABASES");
while ($row = mysql_fetch_assoc($res)) {
    echo $row['Database'] . "\n";
}

=== other way ===

one another long query which also result all database name


SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA


here SCHEMA_NAME is the database name
MySQL store all meta data(data about data) at INFORMATION_SCHEMA
INFORMATION_SCHEMA database have SCHEMATA table , which have following column

CATALOG_NAME
SCHEMA_NAME
DEFAULT_CHARACTER_SET_NAME
DEFAULT_COLLATION_NAME
SQL_PATH

its all about database . it store database 's character set , collation name and sql path and database 's category .

PHP code to get database list from INFORMATION_SCHEMA database


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

if ($result = $mysqli->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA")) {

        if($result){
                     // Cycle through results
                    while ($row = $result->fetch_object()){
                echo $row ."\n";
            }
            // Free result set
            $result->close();
}

$mysqli->close();
?>




if you are using older version of php than

$res = mysql_query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA");

while ($row = mysql_fetch_assoc($res)) {
    echo $row['SCHEMA_NAME'] . "\n";
}

Post Answer