推/弹当前数据库

Question

I have a simple PHP / MySql application which will generally pick one of several databases (let's say one per customer) to manipulate. However, there are frequent calls to utility functions which access a common database.

I don't want to sprinkle USE clauses throughout my code, so it looks like I ought to push the current database at the start of each utility function and pop it again at the end. Something like this (from the top of my head, so prolly won't work, but will give an idea).

function ConnectToDatabase($db)
{
   global $current_database;
   $current_database = $db;
   odb_exec('USE ' . $db);  // etc. Error handling omitted for clarity
}

function UtilityFunction()
{
   odb_exec('USE common_db');  // etc. Error handling omitted for clarity
   // do some work here
   global $current_database;
   ConnectToDatabase($current_database);
}

Maybe I can make it prettier by combining global $current_database; ConnectToDatabase($current_database); into a PopCurrentDb function, but you get the picture.

is this better done in PHP? Is there a MySql solution (but later I want to be ODBC compliant, so maybe PHP is better). How do others do it?

---

Update: in the end I just decided to always fully qualify access,
e.g. SELECT * from $database . '.' . $table

Answer 1

Why dont you just make some kind of database manager class and just push that around? Centralize all you dbname/connection storage in a single entity. that way you have a clear api to access it and you can just use the db by name.

class MultiDb
{

   /*
    * Array of PDO DB objects or PDO DSN strings indexed by a connection/dbname name
    *
    * @var array
    */
   protected $connections = array();

   /*
    * The connection name currently in use
    * @var string
    */
   protected $currentConnection;

   /*
    * The Defualt connection name
    *
    * @var string
    */ 
   protected $defaultConncetion;

   /*
    * @param array $connections Any array DSN or PDO objects
    */ 
   public function __construct(array $connections);

   public function getConnection($name);

   // i would set this up to intelligently return registered connections
   // if the argument matches one
   public function __get($name)

   // same with __set as with __get
   public function __set($name, $value);

   // proxy to the current connection automagically
   // if current isnt set yet then use default so things
   // running through this would actually result in
   // call_user_func_array(array(PDO $object, $method), $args);

   public function __call($method, $args);

}

So usage might look like

// at the beginning of the app

$db = new MultiDb(array(
   'util' => array('mysql:host=localhost;dbname=util;', 'user', 'pass');
   'default' => array('odbc:DSN=MYDSN;UID=user;PWD=pass;');
));


// some where else in the app we want to get some ids of some entities and then
//  we want to delete the associated logs in our shared utility DB

// fetch the ids from the default db

$ids = $db->default->query('SELECT c.name, c.id FROM some_table c')
  ->fetchAll(PDO::FETCH_KEY_PAIR);

// assume we have written a method 
// to help us create WHERE IN clauses and other things
$in = $db->createQueryPart($ids, MultiDb::Q_WHERE_IN); 

// prepare our delete from the utility DB
$stmt = $db->util->prepare(
  'DELETE FROM log_table WHERE id IN('.$in['placeholder'].')', 
  $in['params']
);

// execute our deletion
$stmt->execute();