Multiple Databases using PDO

Question

I'm pretty new to using PDO and I would like to set it up so I can have multiple databases as and when I need them. So I've created a function that allows you to pass a database name to be used as and when.

It does work to a certain extent, as in it selects the database you pass in but even if the database is omitted or incorrect it still allows you to select tables and rows from a database which seems to be selected at random based on the MySQL user.

This isn't a major issue I suppose but I would like to get it to where it won't select any data unless a database has been passed to through my function.

My code is below and I would appreciate your thoughts on how I may better approach this. Thanks.

index.php

require 'app/cream.php';

try {

    $db = new Cream_Model();
    $db = $db->selectDb( 'cream' );

    $data = $db->query('SELECT * FROM users');
    foreach( $data as $row ) {
        print_r( $row );
    }

} catch( PDOException $e ) {

    echo 'An error has occurrred: ' . $e->getMessage() . '<br />';

}

Model.php

class Model {

    public $connection;

    public function connect() {

        try {

            $connection = new PDO( DB_DSN . ':' . DB_HOST, DB_USERNAME, DB_PASSWORD, array( PDO::ATTR_PERSISTENT => true ) );
            $connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $connection->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );

        } catch( PDOException $e ) {

            echo 'An error has occurred: ' . $e->getMessage() . '<br />';
            die();

        }

        return $connection;

    }

}

Cream_Model.php

class Cream_Model extends Model {

    public $conn;

    public function selectDb( $db ) {

        try {

            $conn = $this->connect();
            $conn->exec( "USE $db" );

        } catch( PDOException $e ) {

            echo 'An error has occurred: ' . $e->getMessage() . '<br />';

        }

        return $conn;

    }

}

For PDO, you should NOT exec `USE dbname` directly. Instead, specify dbname in your connection string like ['mysql:host=localhost;dbname=testdb'](http://php.net/manual/en/ref.pdo-mysql.connection.php) — mvp, Feb 18 '13 at 09:25

score 3 · Accepted Answer · answered Feb 18 '13 at 09:30

3

For PDO, you should NOT exec USE dbname directly.

I think what is happening that you have multiple instances of PHP script, and when each one executes USE dbname without PDO being aware of it happening, and this causes whole mess.

Instead, you should specify dbname in your PDO connection string like 'mysql:host=localhost;dbname=testdb'. That means you cannot really switch between databases after creating your Model class. You should know your database name upfront and use it in Model constructor.

Read more in PDO documentation.

answered Feb 18 '13 at 09:30

mvp

111,019
13
122
148

Why should you not use exec( "USE dbname" )? Is there a reason for this? – Gareth Daine Feb 18 '13 at 09:44
1

Because PDO was designed as vendor agnostic database connection layer. Other vendors (Oracle, Postgres, etc) do not have `USE dbname` or its equivalent. By executing USE directly, PDO loses track which database is actually current - don't do that. Instead, you specify dbname in your connection string – mvp Feb 18 '13 at 09:48
OK, that makes sense but there must be a way to use multiple databases correctly. Essentially I'd like a connection class and then a way to pass the database to the connection. – Gareth Daine Feb 18 '13 at 10:05
Yes, that would be OK but I'm trying to automate or at least reduce the code where I need to connect by using a connection class. I've got it working now. I simply removed , array( PDO::ATTR_PERSISTENT => true ) from within the connection string. – Gareth Daine Feb 18 '13 at 10:08
Note that by removing persistent connections, you nullified any performance benefits afforded by them. Now, on every page load, you esablish new connection to the server. In effect, it is the same as opening new db connection for every new page load. If you use multiple connections AND persistent flag, it should work faster that it is right now. – mvp Feb 18 '13 at 10:11
Right, I see. Hmmm, there must be a way of achieving this without creating a new connection string every time. Obviously, you are more experienced than myself so I may be barking up the wrong tree. Thanks for the help so far though. – Gareth Daine Feb 18 '13 at 10:13
Not really, unless you want to patch PDO to support that explicitly, or are willing to use another driver like `mysqli` (but it will also lose database abstraction that PDO is good for). – mvp Feb 18 '13 at 10:17
OK, fair comment. Looks like I'm approaching this in the wrong way. Thanks for your advice. – Gareth Daine Feb 18 '13 at 10:18

Multiple Databases using PDO

1 Answers1

Linked