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.
$this->db->join() Multiple function calls can be made if you need several joins in one query. If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, right outer and full outer.
Inner joins combine records from two tables whenever there are matching values in a field common to both tables. You can use INNER JOIN with the Departments and Employees tables to select all the employees in each department.
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.
I'm having table x in DB1 which contain id, name and y_id. In DB2 there is a y table with id and name
so if you need to get name of x and y in a same query the here is your answer:
$db = $this->load->database("DB2", TRUE);
$DB = $this->load->database("DB1", TRUE);
$DB->select('x.name as name1, y.name as name2');
$DB->from('x');
$DB->join($db->database.'.y','x.y_id = y.id');
$res = $DB->get();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With