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

Question

I want to select a MySQL database to use after a PHP PDO object has already been created. How do I do this?

// create PDO object and connect to MySQL $dbh = new PDO( 'mysql:host=localhost;', 'name', 'pass' );  // create a database named 'database_name'  // select the database we just created ( this does not work ) $dbh->select_db( 'database_name' ); 

Is there a PDO equivalent to mysqli::select_db?

Perhaps I'm trying to use PDO improperly? Please help or explain.

EDIT

Should I not be using PDO to create new databases? I understand that the majority of benefits from using PDO are lost on a rarely used operation that does not insert data like CREATE DATABASE, but it seems strange to have to use a different connection to create the database, then create a PDO connection to make other calls.

Answer 1

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.