Connecting to multiple databases

[eluser]dave80[/eluser]
I know how to connect to multiple databases simulataneouly, with CI.
However my question here, is it possible to connect to different database.

For example a user/client logs in, via a common database, which containe ALL client ids.
Depending on this client id, a session variable is established with the database they belong to. Depending on that session variable, whatever query takes place, occurs in that specific database.

I hope this mad sense. I just dont see the sense in connecing to multiple databases, when they are over 100 clients which equals 100 db. (each client has their own database, with one generic db, that contains ALL user/id/pass and DB they belong to.)

Thank you for any replies

[eluser]TheFuzzy0ne[/eluser]
Assign the model a variable to store it's database object. You can use a global instead if you wish.

Code:

$this->CLIENT_DB = $this->load->database('client7', TRUE);

If you want to change databases, then you would simply reassign the new database to the global variable:

Code:

$this->CLIENT_DB = $this->load->database('client28', TRUE);

Your code can be structured to deal with the generic client database ($CLIENT_DB). It wouldn't need to know which one it's working on, you'd just have to make sure that you've loaded the right one before using it, and do any error checking at that time.

Code:

class MY_client_DB extends Model {

    var $_CLIENT_DB = FALSE; // The database object
    var $cid = FALSE; // Client's ID

    function Some_model($client_id)
    {
        parent::Model();
        $this->initialize($client_id);
    }

    function initialize($client_id)
    {
        // Load the clients database
        $this->_CLIENT_DB = $this->load->database("client" . $client_id);

        // If we get a FALSE, return FALSE
        if ($this->_CLIENT_DB == FALSE) { return FALSE; }

        // Otherwise, set the client ID for the model, and return TRUE
        $this->cid = $client_id;
        return TRUE;
    }
}

extend that class with your actual model

Code:

class Client_db extends MY_Client_db {

    // Update the clients username
    function updateClientUsername($new_username)
    {
        $this->_CLIENT_DB->where('id', $this->cid);
        $this->_CLIENT_DB->update('clients', array('username' => $new_username);
    }

    // Update the clients address
    function updateClientAddress($data)
    {
        $this->_CLIENT_DB->where('client_id', $this->cid);
        $this->_CLIENT_DB->update('client_addresses', $data);
    }

}

Load the model from within your controller and supply the client ID to the constructor (after you know it's valid, of course):

Code:

$this->load->model('client_db', $client_id);

You will get an error if you don't supply a client ID. It's up to you to ensure that the model has loaded successfully. Assuming you've loaded your model with the credentials above, you can check like this:

Code:

if ($this->client_db->cid === FALSE)
{
    // Uh-oh. No CID so either the database configuration for them doesn't exist.
    ...
}
else
{
    // Sweet. We're good to go!
    ...
}

The code above is untested. It serves only to help illustrated my thoughts.
I hope this helps.

[eluser]dave80[/eluser]
Thanks the "TheFuzzy0ne", that was great! After a few code changes, it works perfect.

[eluser]TheFuzzy0ne[/eluser]
Glad to be of service.

[eluser]janogarcia[/eluser]
A couple of questions about TheFuzzyOne's reply:

[quote author="TheFuzzy0ne" date="1234379582"]

Load the model from within your controller and supply the client ID to the constructor (after you know it's valid, of course):

Code:

$this->load->model('client_db', $client_id);

You will get an error if you don't supply a client ID. It's up to you to ensure that the model has loaded successfully.[/quote]

According to the user guide, the second parameter in the model loading method is for specifying a name/alias for the model:

If you would like your model assigned to a different object name you can specify it via the second parameter of the loading function:

Code:

$this->load->model('Model_name', 'fubar');

$this->fubar->function();

So, Would your snippet work as you expected? (second parameter as an argument for the model constructor instead of the model name)

The other question is about Client_db extending MY_Client_db. In order this to work $this->load->model('client_db', $client_id); How would you ensure that the MY_client_DB is already loaded? Autoloading MY_Client_DB model? Requiring it in Client_db model? Any other?

Thanks!

[eluser]TheFuzzy0ne[/eluser]
[quote author="janogarcia" date="1244908232"]According to the user guide, the second parameter in the model loading method is for specifying a name/alias for the model:
[/quote]

You are totally correct. That was just an error of omission on my part. I did mention that the code was untested. The purpose of my post was merely to illustrate the concept.

[quote author="janogarcia" date="1244908232"]So, Would your snippet work as you expected?[/quote]

Evidently not...

[quote author="janogarcia" date="1244908232"]The other question is about Client_db extending MY_Client_db. In order this to work $this->load->model('client_db', $client_id); How would you ensure that the MY_client_DB is already loaded? Autoloading MY_Client_DB model? Requiring it in Client_db model? Any other?[/quote]

It depends. If the class was a base class, then I'd use require_once(), if the class was meant to be used as is, I'd load it via the loader. In this instance, the MY_ prefix is useless, since you're not extending any of CodeIgniter's native classes. When classes have a MY_ prefix, it suggests to me that it will extend the native class (CodeIgniter does this automatically), so if the name that follows the MY_ prefix is not the name of a native CodeIgniter class, then there's no point in adding the prefix.

Hope this helps.

[eluser]janogarcia[/eluser]
Thanks for the fast and detailed reply!

(I was aware about the MY_ prefix being not required in this case, but I just copied the snippets as they were)