CODEWITHSUNDEEP
codeigniter
CopperRabbit
CopperRabbit

Reputation: 674

Codeigniter dynamically change to undefined Database

I need to connect to a DB that has not been defined in the database.php config file.

It has not been defined because there are over 200 possible databases.

I will loop through my clients table to get their client code that will determine which database will be in use at the moment.

I generate the db name with a string inside the loop like this:

$db_name = "db_".$row['code'];

From this, I need to connect to a new DB which will loop through the tables of the DB to check if this DB and the master DB are synchronized.

I tried the following code, but it does not seem to work:

$this -> db -> database = $db_name;

and I also tried to define a blank connection in the config like this:

$db['temp'] = $db['default'];
$db['temp']['database'] = "";

and then change to the generated DB name with the 

$this -> db -> database = $db_name;

function, but did not work.

I am not sure if this is at all possibe, but this was all I could find on how to switch DB connections.

If you have any alternative suggestions, please let me know.

Thanx is advance

Upvotes: 1

Views: 62

Answers (1)

MackieeE
MackieeE

Reputation: 11862

The switching of Databases you may have found in the CodeIgniter Documentation is probably the only, sensible way of obtaining the information without heavily bloating the config files. So hypothetically speaking: 

$db_config = array(
    //Shared values
    'hostname' => '...',
    'dbdriver' => '...';
);

$all_results = array();

$getDatabases = $this->database_model->get_databases();
foreach( $getDatabases as $key => $row )
{
    $client_db = null;
    $db_forge  = null;

    $db_name   = "db_". $row['code'];

    //Use copy of shared DB Values.
    $active_config = $db_config;
    $active_config['database'] = $db_name;

    $client_db = $this->load->database( $active_config, TRUE );
    $db_forge  = $this->load->dbforge( $client_db, TRUE );

    $result = 
       $client_db->select( "*" )
                 ->get_where( "TB_EXAMPLE", array( "client_id" => '...' ))->row();

    //Example of reason to drop the table
    if ( !empty( $result ) && $result->INACTIVE == TRUE )
    {
         $db_forge->drop_table( "TB_EXAMPLE" );
    }

    //Now finished with this connection to this particular Database.
    $client_db->close();
    $all_results[] = $result;
}

However this is pretty ill-advised. It'll obviously connect to 200 different databases, intensive and expensive. If they are all on the same server; it would be better to SELECT by different databases, than instead connecting to them.

Upvotes: 1

Related Questions