Codeigniter - Multiple database prefixes

Question

I'm in process to migrate an old webapp to codeigniter, everything looks good other then that the database has 3 kind of tables with different table prefixes.

the app's native tables use mci_ as prefix
tables which hold inter departmental data (kind off) use mdept_
and tables handling non-native data use mext_ as prefix

now as I understand we can set one prefix for entire database

'hostname' => DB_HOST,
'username' => DB_USER,
'password' => DB_PASS,
'database' => DB_NAME,
'dbdriver' => 'mysqli',
'dbprefix' => 'mci_',

and query builder will use mci_ for all tables.

My question is, is there a way to use rest of the 2 prefixes (mdept_, mext_) with query builder rather then having the direct approach db->query

Thanks

Elena Vasilenko · Accepted Answer · 2019-08-10 10:26:28Z

1

Solution #1:

You can set $this->db->dbprefix = "mdept_" before $this->db->select()...get();. But this will work if there is only one table in the query. And if there are other queries below, then you also need to redefine dbprefix again.

Solution #2:

Add for mdept_... and mext_... tables the prefix mci_. I.e mci_mdept_users. No need to change the database config, redefine the prefix, just specify the table names in the queries along with the old "prefixes".

Solution #3:

Delete prefix in config at all: 'dbprefix' => '', and use full table names with prefixes in queries.

Solution #4. The best solution of these, in my opinion

Add a view with the mci_ prefix to your database: CREATE VIEW mci_old_mdept_users AS SELECT * FROM mdept_users;. All that the view will do is to get data from a table that you cannot rename. And in your application you can write simply: $this->db->get('old_mdept_users');.

Solution #5. If all queries use tables with the same prefixes

Divide tables into 3 databases. Add these databases in the config:

$db['default'] = array(
    ...
    'dbprefix'     => 'mci_',
    ...
);
$db['mdept'] = array(
    ...
    'dbprefix'     => 'mdept_',
    ...
);
$db['mext'] = array(
    ...
    'dbprefix'     => 'mext_',
    ...
);

Add other connections in MY_Model:

protected function connect_mdept_db()
{
    $this->mdept_db = $this->load->database('mdept', TRUE);
}

Use these connections:

$this->connect_mdept_db();
$this->mdept_db->get("users"); // uses "mdept_users" table

edited Aug 10, 2019 at 10:26

answered Aug 9, 2019 at 10:12

Elena Vasilenko

2361 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anupam Over a year ago

can't change prefix (solution 2) as specially the non-native mext_ tables are being populated external data by foreign scripts I have no control over! I think with solution 1 you are suggesting to set dbprefix each time I have to use those tables right?

Elena Vasilenko Over a year ago

@Anupam Yes, that's right. If there are many such places, then Solution 3 or Solution 4 is probably better, I added in the answer above.

Anupam Over a year ago

don't like solution 3, yet maybe the only way if I want to use active records! About solution 4, not practical in case since there are 20+ mdept_ tables and 65+ mext_ ones!! yeah it's that crazy!

Elena Vasilenko Over a year ago

@Anupam If you want use Active Record AND in one query you have tables with different queries AND don't want use views - yes, only Solution 3. If all queries use tables with the same prefixes, then Solution 5 may suit you, added above.

Anupam Over a year ago

Yep I've thought of setting multiple db configs, I'm actually headed that way. Thanks for confirming the same :D

Collectives™ on Stack Overflow

Codeigniter - Multiple database prefixes

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related