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
Get Last Word from Url After a Slash in PHP
How to Determine If an Array Has Any Elements or Not
Key of Null Variable Equals Null Not Error
Namevaluepair Error "Namevaluepair Cannot Be Resolved to a Type"
Add Custom Columns to Admin Products List in Woocommerce 3
Firefox Error 'No Element Found'
Yii2 Links Between Frontend and Backend (Advanced Template)
Php:Difference Between '&&' and 'And'
How to Send Https Posts Using PHP
In PHP How to Access a ":Private" Array in an Object
PHP Twitter Replace Link and Hashtag with Real Link
Add X Days to Date, Excluding Weekends
How to Access MySQLi Connection in Another Class on Another Page
Woocommerce New Order Action - Get Order Information