I need to write a join query of two tables from two databases and fetch the joined data. For eg, consider I have a database db1 which has some tables named companies, plans, customers. Suppose I need to join the two tables companies and plans with another table 'cdr' on another database db2 by grouping them using a similar column.
The query which I'm running right now is given below:
function get_per_company_total_use ($custid)
{
$this->DB1->select('ph_Companies.CompanyName');
$this->DB1->where('ph_Companies.Cust_ID', $custid);
$this->DB2->select_sum('cdr.call_length_billable')->from('cdr');
$this->DB2->group_by('cdr.CompanyName');
$this->db->join('Kalix2.ph_Companies', 'Kalix2.ph_Companies.CompanyName = Asterisk.cdr.CompanyName');
$query = $this->db->get();
if($query->result()){
foreach ($query->result() as $value) {
$companies[]= array($value->CompanyName,$value->call_length_billable);
}
return $companies;
}
else
return FALSE;
}
But my query is not fetching the data and throwing an error. This same query, I have run on a single database and is working. But I need help to find how this can be done with two databases.
You can just give the following if you need to join two database tables:
function get_per_company_total_use ($custid)
{
$this->db->select('Kalix2.ph_Companies.CompanyName');
$this->db->where('Kalix2.ph_Companies.Cust_ID', $custid);
$this->db->select_sum('Asterisk.cdr.call_length_billable')->from('Asterisk.cdr');
$this->db->group_by('Asterisk.cdr.CompanyName');
$this->db->join('Kalix2.ph_Companies', 'Kalix2.ph_Companies.CompanyName = Asterisk.cdr.CompanyName');
$query = $this->db->get();
if($query->result()){
foreach ($query->result() as $value) {
$companies[]= array($value->CompanyName,$value->call_length_billable);
}
return $companies;
}
else
return FALSE;
}
Here actually you need not give the connection variable DB1 or DB2, just give $this->db.