how to get array of databases in localhost using PDO [duplicate]

Question

trying to get an array of databases in localhost using PDO

$user = 'root';
$server = 'localhost';
$db = new PDO("mysql:host=$server", $user);

$sql = "show databases";
$st = $db->prepare($sql);
$st->execute();
$arr = $st->fetchAll(PDO::FETCH_ASSOC);
foreach($arr as $el){
    echo $el . '<br>'; // error - array to string conversion
}

also tried:

 $arr = $st->fetch(PDO::FETCH_ASSOC);

result - only the first database is echoed

Any help?

You can use var_dump($el); to see what was returned from the statement - since you're asking for FETCH_ASSOC, an associative array will be returned with a key for each value you're looking for. — MatsLindh
– MatsLindh, Commented Mar 8, 2020 at 14:40
just replace echo $el . '<br>'; => print_r($el) . '<br>'; — Ronak Dhoot
– Ronak Dhoot, Commented Mar 8, 2020 at 14:53

Bernd Buffen · Accepted Answer · 2020-03-08 14:48:18Z

1

Get the Information from the information schema like:

SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`;

There are also more infos about the databases (schemas)

sample

MariaDB [(none)]> SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| bernd              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

answered Mar 8, 2020 at 14:48

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qadenza Over a year ago

and how to get the array from the above query?

Nigel Ren Over a year ago

If this is a query in PHP PDO, you can use $stmt->fetchAll(PDO::FETCH_COLUMN);

toh19 · Accepted Answer · 2020-03-08 14:55:50Z

1

Just loop through the result and print it:

while ($arr = $stmt->fetch(PDO::FETCH_ASSOC)){
    print_r($arr);
}

The result is going the name of databases in your localhost. For example:

Array
(
    [Database] => mysql
)
Array
(
    [Database] => performance_schema
)
Array
(
    [Database] => phpmyadmin
)

Edit:

$databases = [];
while ($arr = $stmt->fetch(PDO::FETCH_ASSOC)){
    $databases[] = $arr["Database"];
}

edited Mar 8, 2020 at 14:55

answered Mar 8, 2020 at 14:47

toh19

1,23912 silver badges24 bronze badges

6 Comments

qadenza Over a year ago

thanks but I need the array as a variable for other functions

toh19 Over a year ago

What do you mean array as a variable? like each row will print the name of the database as a string variable and not an array?

qadenza Over a year ago

like this - $arr = ['firstdb', 'seconddb'...]

qadenza Over a year ago

it works now, thanks. Just a note - I have now two loops - while for getting array and foreach for echoing. I hope there is a way to have only one of them.

toh19 Over a year ago

Remove your foreach loop

|

Collectives™ on Stack Overflow

how to get array of databases in localhost using PDO [duplicate]

2 Answers 2

2 Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Linked

Related