Is it possible to run a JOIN request with multiple database connections using CodeIgniter?
Is it possible to combine multiple tables from different databases using CodeIgniter's active record?
I understand that I need to create two separate database groups in database.php
and load them manually inside the model:
$this->DB1= $this->load->database('somename1', TRUE);
$this->DB2= $this->load->database('somename2', TRUE);
However, I don't know how to use them to combine multiple tables from two separate databases.
How can i do this?
source to share
As far as I know there is no way to do this using multiple database instances (i.e. variables $DB1
and $DB2
).
However, if you have a user with access to both databases, you can prefix your table names with your database names and it will work (at least with MySQL - I haven't tested anything yet). For example, this code:
$this->load->database('first');
$this->db->select('*')->from('users');
$this->db->join('test.hometowns', 'users.id = second.hometowns.user');
$query = $this->db->get();
Will execute this query successfully, returning expected results:
SELECT *
FROM (`users`)
JOIN `second`.`hometowns` ON `users`.`id` = `second`.`hometowns`.`user`;
Again, I've only tested this with MySQL, and other databases may have database boundary crossing restrictions. This also requires the user account to have read permissions on both databases and tables.
source to share