How to Get a List of MySQL Databases in PHP Using Pdo

How can I get a list of MySQL databases in PHP using PDO?

You can use

show databases

or a query on the information_schema:

select schema_name from information_schema.schemata

how to get array of databases in localhost using PDO

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)]>

How do I select a MySQL database to use with PDO in PHP?

Typically you would specify the database in the DSN when you connect. But if you're creating a new database, obviously you can't specify that database the DSN before you create it.

You can change your default database with the USE statement:

$dbh = new PDO("mysql:host=...;dbname=mysql", ...);

$dbh->query("create database newdatabase");

$dbh->query("use newdatabase");

Subsequent CREATE TABLE statements will be created in your newdatabase.


Re comment from @Mike:

When you switch databases like that it appears to force PDO to emulate prepared statements. Setting PDO::ATTR_EMULATE_PREPARES to false and then trying to use another database will fail.

I just did some tests and I don't see that happening. Changing the database only happens on the server, and it does not change anything about PDO's configuration in the client. Here's an example:

<?php

// connect to database
try {
$pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch(PDOException $err) {
die($err->getMessage());
}

$stmt = $pdo->prepare("select * from foo WHERE i = :i");
$result = $stmt->execute(array("i"=>123));
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

$pdo->exec("use test2");

$stmt = $pdo->prepare("select * from foo2 WHERE i = :i AND i = :i");
$result = $stmt->execute(array("i"=>456));
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

If what you're saying is true, then this should work without error. PDO can use a given named parameter more than once only if PDO::ATTR_EMULATE_PREPARES is true. So if you're saying that this attribute is set to true as a side effect of changing databases, then it should work.

But it doesn't work -- it gets an error "Invalid parameter number" which indicates that non-emulated prepared statements remains in effect.

Using PDO to get a list of how many instances of an item are in a MySQL database column

For anyone who may be struggling with this concept, here is what I came up with so far. I'm sure there are cleaner methods of doing this, but it seems to work fine for now. Since I am also trying to incorporate more object oriented coding into my bag of tricks, I ended up putting the business end of the solution into a function. If I accumulate a couple more as I rebuild my site I will probably end up grouping them together into a PDO wrapper class for myself, or something of that nature.

Here is my function:

function fieldCount($field,$condition,$table)
{
//This is just a file that holds the database info and can be whatever name you want
require('database.ini');
try{
$dbh = new PDO("mysql:host=$db_hostname;dbname=$db_database", $db_username, $db_password);

//This turns on the error mode so you get warnings returned, if any
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbh->prepare("SELECT $field, COUNT(*) AS count FROM $table $condition GROUP BY $field");

// I tried to bind my $field variable but for some reason it didn't work so I
// commented it out below until I can look deeper into it. If anyone sees any
// glaring errors, please point them out. It looks right to me, but as I said,
// it's not working.

// $stmt = $dbh->prepare("SELECT :field, COUNT(*) AS count FROM $table $condition GROUP BY :field");
// $stmt->bindParam(':field', $field);

$stmt->execute();

$results=$stmt->fetchAll(PDO::FETCH_ASSOC);
// Creates an array similar as the following:
// $results[0][$field] $results[0]['count']
// $results[1][$field] $results[1]['count']
// $results[2][$field] $results[2]['count']
// with each row as an array of values within a numeric array of all rows

$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

return $results;
}

The database.ini file might look something as simple as this:

<?php
//database.ini

$db_hostname = 'myHostname';
$db_database = 'databaseNameHere';
$db_username = 'YourUserNameHere';
$db_password = 'YourPasswordHere';
?>

Here is how you call the function:

<?php
// First you have to "require" your file where the function is located,
// whatever you name it.
require('FieldCountFunction.php');

// Determine what column in your database you want to get a count of.
// In my case I want to count how many items there are in each individual
// category, so the $field is Category here.
$field = 'Category';

// If there is a condition to your query, you would state it here.
// In my case I wanted to exclude any items in my Inventory table that
// are marked "Discontinued" in the Notes column of my database.
$condition = "WHERE Notes NOT LIKE 'Discontinued'";

$DB_Table = 'Inventory';

// Now you call the function and enter the $field you want to get a count of,
// any conditions to the query, and the database table name you are querying.
// The results you collected in the function (it was called $results above),
// will be dumped into a new array now called "$fieldArray" below.
$fieldArray = fieldCount($field, $condition, $DB_Table);

// To get the data out again you can do the following to cycle through the
// numeric array of rows (that's what the "$i" is for) and from each numbered
// row you can retrieve the "$field" and "count" that you need. Then display
// them however you want. I'll just echo them out here.
$i=0;
while($i<count($fieldArray))
{
echo $fieldArray[$i]["$field"].' : '.$fieldArray[$i]['count'].'<br />';
$totalCount = $totalCount + $fieldArray[$i]['count'];
$i++;
}
echo $totalCount.' items total';
?>

This will give results similar to the following:

Accessories : 638

Markers : 432

Masks : 146

Packs : 93

Paint : 47

1356 items total

I hope this helps someone out there. And if anyone can shed some light as to why my binding didn't work, I'd appreciate it. If not, I'm sure I'll figure it out eventually.

PHP - Get list of databases names

$result = mysqli_query($db_conn,"SHOW DATABASES"); 
while ($row = mysqli_fetch_array($result)) {
echo $row[0]."<br>";
}

Can I create a database using PDO in PHP?

Yes, you can.

The dsn part, which is the first parameter of the PDO constructor, does not have to have a database name. You can simply use mysql:host=localhost. Then, given you have the right privilege, you can use regular SQL commands to create a database and users, etc.

Following is an example from an install.php file. It logs in with root, create a database, a user, and grant the user all privilege to the new created database:

<?php

$host = "localhost";

$root = "root";
$root_password = "rootpass";

$user = 'newuser';
$pass = 'newpass';
$db = "newdb";

try {
$dbh = new PDO("mysql:host=$host", $root, $root_password);

$dbh->exec("CREATE DATABASE `$db`;
CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass';
GRANT ALL ON `$db`.* TO '$user'@'localhost';
FLUSH PRIVILEGES;")
or die(print_r($dbh->errorInfo(), true));

}
catch (PDOException $e) {
die("DB ERROR: " . $e->getMessage());
}
?>

How to show MySQL databases on a PHP script?

You need to retrieve a result set from the query, like so:

$set = mysql_query('SHOW DATABASES;');
$dbs = array();
while($db = mysql_fetch_row($set))
$dbs[] = $db[0];
echo implode('<br/>', $dbs);

Echo last 10 results of mysql database using pdo

You need to remove prepare statement. and run query directly inside query function.

    $db2 = new PDO('mysql:host='. $host .';dbname='.$db_name_uroll, $db_username, $db_password);

//Remove $db2 prepare
$statement2 = "select * from `{$user}` ORDER BY bet_id DESC LIMIT 2";

if ($stmt = $db2->query($statement2)) //PDO::query() returns a PDOStatement on success or false on failure.
{
//If we got a PDOStatement as a return value from PDO::Query() !!!ECHO WHILE FETCHING!!!
while($row2 = $stmt->fetch(PDO::FETCH_ASSOC)) //This loop will keep going for as many rows as the PDOStatement returns.
{
echo $row2['roll'] . "<br />";
}
}
else
{
//If PDO::Query returned false, then something is wrong with our query. Or connection or whatever.
echo "Query failed.";
}

This code is working.

Docs link: http://www.php.net/pdo.query

Using prepare you have to use execute method.

http://www.php.net/manual/en/pdo.prepare.php



Related Topics



Leave a reply



Submit