CODEWITHSUNDEEP
phpcodeigniterjoinmysqli
Mark
Mark

Reputation: 41

Codeigniter join from multiple databases

Currently I have this not working code in my model:

$this->db_site->order_by('id', 'random');
$select = $this->db_site->get_where('berichten', array('beantwoord' => 0, 'gelezen' => 0));   
$this->db->join('replymessages', 'replymessages.msgid != berichten.id');
$this->db->join('replymessages', 'replymessages.time < '.time());

In this example the joins won’y work for the db_site query’s, is there a methode to do a join from an other database?

Upvotes: 2

Views: 2588

Answers (2)

Vamsi Krishna B
Vamsi Krishna B

Reputation: 11490

In /config/database.php

Create a new set of configuration for db connection

$db['seconddb']['hostname'] = 'localhost';
$db['seconddb']['username'] = 'krish';
$db['seconddb']['password'] = 'pass';
$db['seconddb']['database'] = 'oOoOoO';
$db['seconddb']['dbdriver'] = 'mysql';
$db['seconddb']['dbprefix'] = '';
$db['seconddb']['pconnect'] = TRUE;
$db['seconddb']['db_debug'] = TRUE;
$db['seconddb']['cache_on'] = FALSE;
$db['seconddb']['cachedir'] = '';
$db['seconddb']['char_set'] = 'utf8';
$db['seconddb']['dbcollat'] = 'utf8_general_ci';
$db['seconddb']['swap_pre'] = '';
$db['seconddb']['autoinit'] = TRUE;
$db['seconddb']['stricton'] = FALSE;

Loading the db

$this->load->database('seconddb');

To connect to multiple databases

$db1 = $this->load->database('default', TRUE);
$db2 = $this->load->database('seconddb', TRUE);

and the syntax changes a lil bit for active records.

Instead of 

$this->db->query();

you should use 

$db1->query();

and

$db2->query();

so ,just edit your queries as required.

Upvotes: 1

Dan Smith
Dan Smith

Reputation: 5685

There is no built in method to do the join I'm afraid no. If databases are in the same database instance and you can grant a user access to both databases you can join that way by prefixing the database name on the table, i.e. database1.table1.

The alternative is to do the join the data using an in-php only solution.

Upvotes: 2

Related Questions