Query table in a different database

Question

I'm writing another plugin for my craft app, this time I'm attempting to reuse a MySQL table that's used by other applications as well. I've a class extending the BaseRecord, and getTableName() will return differentdb.tablename. Does Craft handle this, or do I have to make a copy of that table in the craft database? (MySQL permissions for the craft user already give it read access to that database)

Answer 1

Have you tried it? I'm pretty sure that's not going to work as Active Record is tied to the db connection in craft/config/db.php hanging off of craft()->db.

You should be able to pull it off with a plugin that instantiates its own DbConnection, though and uses Query Builder instead of Active Record. Completely untested, but something like this:

$otherDbConnection =  Craft::createComponent(array(
    'emulatePrepare'    => true,
    'charset'           => 'utf8',
    'tablePrefix'       => 'craft_',
    'class'             => 'Craft\DbConnection',
    'autoConnect'       => true,
));

$otherDbConnection->connectionString = 'mysql:host=localhost;dbname=otherdb;port=3306';
$otherDbConnection->username = $userName;
$otherDbConnection->password = $password;

From there you can do $otherDbConnection->createCommand() and grab all of the data you want from the other database. It's up to you how you'd merge that in with data from your existing database.

If you wanted to get fancy and access the database in other files/places in code, you could do:

craft()->setComponent('otherDb', $otherDbConnection);

And access the other database via: craft()->otherDb->createCommand().