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

40
votes

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.

4
phpmysqlpdo
You will probably need to create a new PDO object to connect to another database. - Ignas
You can use select table.field from database.table, assuming that whatever user ID you've connected with has the appropriate rights on the other db/tables. - Marc B
What's wrong with creating a new object? Once you run your queries like "CREATE DATABASE" you can easily destroy it and continue working with a new connection. I don't really see an issue with it. - Ignas
That just seems like a silly thing to have to do, which generally means I'm doing something else wrong. Maybe this is just an edge case. PDO is probably not often used to create new databases. - T. Brian Jones
@Ignas: Takes about 23ms to open a connection to a database (instantiate PDO) in my tests. I have literally hundreds of databases; it makes my script take 4x longer than using a use statement. - mpen

4 Answers

54
votes

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.

1
votes

You should be setting the database when you create the PDO object. An example (from here)

<?php
$hostname = "localhost";
$username = "your_username";
$password = "your_password";

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
    echo "Connected to database"; // check for connection
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>
0
votes

Alternatively, you can select a MySQL database to use after a PHP PDO object has already been created as below:

With USE STATEMENT. But remember here USE STATEMENT is mysql command

try
{
    $conn = new PDO("mysql:host=$servername;", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $conn->exec("use databasename");
    //application logic
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

I hope my code is helpful for requested

-1
votes

As far as I know, you have to create a new object for each connection. You can always extend the PDO class with a method which connects to multiple databases. And then use it as you like:

public function pickDatabase($db) {
  if($db == 'main') {
    return $this->db['main']; //instance of PDO object
  else
    return $this->db['secondary']; //another instance of PDO object
}

and use it like $yourclass->pickDatabase('main')->fetchAll('your stuff');