Codeigniter 3 multiple dynamic database mysql

Question

I have several clients that have the same database architecture (mysql) and the same solution (no specific development) problem that now I do directories for each client and in each config.php file I connect to Database as follows:

For client x:

$db['default'] = array(
    'dsn' => '',
    'hostname' => 'localhost',
    'username' => '***',
    'password' => '***',
    'database' => 'comuniksales_x',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

and for client y :

$db['default'] = array(
        'dsn' => '',
        'hostname' => 'localhost',
        'username' => '***',
        'password' => '***',
        'database' => 'comuniksales_y',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => (ENVIRONMENT !== 'production'),
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
    );

As you see I change only the name of the database

N.B: that in each database there are the identifiers of the clients

I have added a new text field in the authentication page for the client to enter the account (ex: client x -> x, client y -> y, ...) so that I can retrieve x and To put in the config but i arrive not field in the authentification

My question is simple how I can use a single solution (not for each client its own folder) for several clients who have each a dedicated database I explain: when authentication the client entered his login and password I want him to also enter his account and with this last I know to which database I connect I make the connection and I verify his login And password and all the rest of the processing remains with the connection done in the authentication page — Med Sniper
– Med Sniper, Commented May 24, 2017 at 14:00

Atural · Accepted Answer · 2017-05-24 14:36:21Z

A possible approach would be to set up a MY_Model

something like this should do the job

class MY_Model extends CI_Model
{
    protected $customDb;

    public function __construct()
    {
        parent::__construct();
        $this->loadCustomerDatabase();
    }

    private function loadCustomerDatabase()
    {
        //define your customers database
        $customerDatabase = $this->session->userdata("customer_db");

        $config['hostname'] = 'localhost';
        $config['username'] = 'myusername';
        $config['password'] = 'mypassword';
        $config['database'] = $customerDatabase;
        $config['dbdriver'] = 'mysqli';
        $config['dbprefix'] = '';
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $config['cache_on'] = FALSE;
        $config['cachedir'] = '';
        $config['char_set'] = 'utf8';
        $config['dbcollat'] = 'utf8_general_ci';
        $this->customDb = $this->load->database($config, true);


    }
}

and all your models should extend MY_Model and you can directly access to your customDB.

GeorgeGeorgitsis · Accepted Answer · 2017-05-24 14:31:31Z

1

You have to set a different index for your second database, like $db['customer_y'] so you will have 2 databases, $db['default'] and $db['customer_y'] in your config/database.php example:

$db['client_x'] = array(
    'dsn' => '',
    'hostname' => 'localhost',
    'username' => '***',
    'password' => '***',
    'database' => 'comuniksales_x',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

$db['client_y'] = array(
        'dsn' => '',
        'hostname' => 'localhost',
        'username' => '***',
        'password' => '***',
        'database' => 'comuniksales_y',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => (ENVIRONMENT !== 'production'),
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
    );

Then in your Controller (or where you grab the input if client = x or y), if the client is x, you do nothing, you use the default. But if the client is y, you load the $db['customer_y'] like below:

    $customer = $this->input->post('customer'); //or how you grab the customer
        if($customer == 'x') {
            $this->db = $this->load->database('customer_x', TRUE);
//here you can store the x in session and use it for the rest Controllers. If you want to use session, you'll have to add the condition in if()
        }elseif($customer == 'y') {
            $this->db = $this->load->database('customer_y', TRUE);
//here you can store the y in session and use it for the rest Controllers. If you want to use session, you'll have to add the condition in if()
        }

You could separate your config/database.php and use indexes customer_x (instead of default) and customer_y if you want to be more specific. Just don't forget to change the $active_group in config/database.php

edited May 24, 2017 at 14:31

answered May 24, 2017 at 14:07

GeorgeGeorgitsis

1,26213 silver badges31 bronze badges

4 Comments

Med Sniper Over a year ago

So according to what I understood I have to have x $db for my clients in the config/database.php and after in each model I do the loading of the database like this $customer_y_db = $this->load->database('customer_y', TRUE); ?

Med Sniper Over a year ago

And the account (x, y, ...) or I stock it in the session?

Med Sniper Over a year ago

Thanks for the help I made it work but when another client connects the first connection this changes with the last logged in connection

Med Sniper Over a year ago

all connexion use the connexion of $active_group = 'default'; $query_builder = TRUE;

DFriend · Accepted Answer · 2017-05-24 14:52:47Z

0

To put all client settings in the same file and select the matching config try this approach.

Here's the database.php config file

$db['clientx'] = array(
  ... all the fields ...
);

$db['clienty'] = array(
  ... all the fields ...
);

$db['clientz'] = array(
  ... all the fields ...
);

You then read the login form for the client name and use that to select the connection config and make the db connection like this.

$client = $this->input->post('clientID');
$this->load->database($client);

You do not need to use the second param to load the database e.g.

$this->load->database($client, TRUE);

because the connection will be assigned to the CI object automatically if you don't use the second param. In other words, it's less work for you.

The only problem I see is if the user posts a client name that does not match one in the database config file. Codeigniter will not load the db if the config is not found and will show an error page. But that does not give you a way to respond gracefully and let the user know what to do. I don't have a quick solution to "validate" the user input before trying to load the db config.

You will probably need to load the config file and check for the "key" yourself before calling $this->load->database. Take a look at the core file /system/database/DB.php to see how the $params argument to function &DB(...) is handled for ideas.

answered May 24, 2017 at 14:52

DFriend

8,9741 gold badge16 silver badges26 bronze badges

5 Comments

Med Sniper Over a year ago

Thanks for the answer the problem is that somebody is logging in, if another client wants to connect the connection of the database remains with the config of the first

Med Sniper Over a year ago

$active_group = 'default'; $query_builder = TRUE;

DFriend Over a year ago

Are you autoloading the database? You should only load it once you have a client login

DFriend Over a year ago

Are you testing multiple logins from separate workstations? If not, the browser will probably see any existing sessions as valid. That seems a possible source of the problem you describe.

Med Sniper Over a year ago

It is good I did it, it is only necessary to save the name of the database and in the constructor of each model I do

function __construct()     {         // Call the Model constructor         parent::__construct();         $this->load->library('session');         $this->db = $this->load->database($this->session->DatabaseConfig, TRUE);     }

Collectives™ on Stack Overflow

Codeigniter 3 multiple dynamic database mysql

3 Answers 3

Comments

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related