JOIN tables from one DB to another DB in Codeigniter

Question

Database.php

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

$db['master'] = array(
    'dsn' => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'secondary_db',
    'dbdriver' => 'mysqli',
    'dbprefix' => 'gap_',
    'pconnect' => FALSE,
    'db_debug' => FALSE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => FALSE,
);

I'm using two databases in Codeigniter 3 and Its working fine. Now I'm facing a problem of JOINing tables across the above databases. Can it be possible or not? If Yes then how to do that?

Example Code:

public function __construct()
{
    parent::__construct();

    $this->db_ecom = $this->load->database('master', TRUE);
}

public function getOrderDetails() {
   ...       
   $this->db->join('countries', 'countries.country_id=orders.order_country', 'left');
   $q = $this->db_ecom->get('orders');
   ...
}

In the above code example, $this->db represents main_db so that $this->db_ecom represents secondary_db.

main_db - contains countries table (country_id, country_name).
secondary_db - contains orders table (with column Country_id).

So Now I want to JOIN this tables to display the Country as Name not as ID.

Possible duplicate of [Join query of two databases in codeigniter](https://stackoverflow.com/questions/14159618/join-query-of-two-databases-in-codeigniter) — Martin, Sep 11 '19 at 12:18
MySQL can do inner databases joins when databases are on the same server `SELECT * FROM database1.table1 INNER JOIN database2.table1 ... ` you don't have to make separated connections like you are doing.. — Raymond Nijland, Sep 11 '19 at 12:22
@Martin I'm not a newbie for StackOverflow, I've already check your answer and its not answer to my question at all. If it looks like really a duplicate, It shows that you didn't red the question in detail. We can use it as a raw query but I want to know it is possible or not? — Silambarasan R, Sep 11 '19 at 12:24
@silambarasanR.D I've just reread your question, and _nowhere_ have you mentioned that you don't want to do this using a standard query — Martin, Sep 11 '19 at 12:26
@silambarasanR.D check mine comment.. Codeignitor's querybuilder should also support that you don't have to use a RAW query for example `from('main_db.countries)` is valid same is possible with the other querybuilder functions aswell.. Also makes `$this->load->database('master', TRUE);` not needed.. — Raymond Nijland, Sep 11 '19 at 12:29
Martin and @RaymondNijland You both were not understood my question. I just need to Join two tables from different Databases using Seperated connections. Is it possible or not. If yes then explain it. thats it. — Silambarasan R, Sep 11 '19 at 12:30
@RaymondNijland You're not saying answer to me. You're just giving suggestion. Thanks for that. But I need answer because I'm not using seperated connection not only for this purpose. — Silambarasan R, Sep 11 '19 at 12:35
i meant -> *"Proof-read before posting! Now that you're ready to ask your question, take a deep breath and read through it from start to finish. Pretend you're seeing it for the first time: does it make sense? Try reproducing the problem yourself, in a fresh environment and make sure you can do so using only the information included in your question. Add any details you missed and read through it again. Now is a good time to make sure that your title still describes the problem!"* — Raymond Nijland, Sep 11 '19 at 12:47

Silambarasan R · Accepted Answer · 2019-09-19T13:13:14.837

As answer from 'Anu' As Martin mentioned this Question as a possible duplicate.

$this->db->join('Kalix2.ph_Companies', 'Kalix2.ph_Companies.CompanyName = Asterisk.cdr.CompanyName');

It will not be executed if we're using tables with PREFIX

DB Structure:

main_db - Table "prefix_countries"
secondary_db - Table "prefix_orders"

Assuming I'm using dbprefix

$this->db->join('main_db.pre_Companies', 'main_db.pre_Companies.CompanyName = secondary_db.pre_cdr.CompanyName', 'left', FALSE); //You can use NULL replacing 'left'. FALSE in last parameter used to execute the query without enclosing Table Prefix.

Solution:

public function getOrderDetails() {
   $this->db_ecom->select("countries.country_id, countries.country_name,.."); //Getting values

// Here `$this->db` is 'main_db' and `$this->db_ecom` is 'secondary'.
   $this->db_ecom->join("{$this->db->database}.{$this->db->dbprefix}countries as countries", "countries.country_id={$this->db_ecom->dbprefix}orders.order_country", 'left', FALSE);

   $q = $this->db_ecom->get('orders'); // No need to use $this->db_ecom->from("order"); seperately as @Raymond Nijland said.
   ...
}

FALSE in last parameter used to execute the query without enclosing Table Prefix. So we've to type the table prefixes manually or we can use $this->db->dbprefix() / $this->db_ecom->dbprefix() method to return Table Prefix.

I'm not hardcoding DB name here. It'll be usefull when you're going live. So I'm using $this->db->database / $this->db_ecom->database method to return Database name.

Note: If you're going live you should create a single user with granted access for both DB else It'll throw Mysql Error.

Thanks to those hard working commenters.
To those guys Stackoverflow is not for Earning Reputations, Badges or Previleges, Its just to know the Answer for the intended Question.

It'll be useful, if you're really facing this kinda situation. Happy coding.

score 0 · Answer 2 · answered Sep 11 '19 at 12:22

0

Did you try this syntax:

$this->db->join('main_db.countries', 'main_db.countries.country_id=secondary_db.orders.order_country', 'left');

answered Sep 11 '19 at 12:22

failedCoder

1,346
1
14
38

Yes I had, but It won't work. :( (500 Internal Server Error) – Silambarasan R Sep 11 '19 at 12:36

JOIN tables from one DB to another DB in Codeigniter

2 Answers2