Asked  7 Months ago    Answers:  5   Viewed   42 times

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.

 Answers

53

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.

Wednesday, March 31, 2021
 
Tapha
answered 7 Months ago
88

Try to check if there are any errors of your mysql on your linux machine (usually located in /var/log/mysql/error.log).

Make sure that the PHP and MySQL version in your Linux machine is the same as your Windows machine in case there are bugs on them in particular version.

Wednesday, March 31, 2021
 
nfechner
answered 7 Months ago
77

It isn't necessarily easy, nor pretty, but this article gives some solutions to your problem: http://www.linux.com/archive/feature/52390

UPDATE

Since the link is gone now, here is more text

Creating a linked server using OLE DB for SQL Server This example creates a linked server named MyDatabase that uses the Microsoft OLE DB Provider for SQL Server.

USE master
GO
EXEC sp_addlinkedserver 
'MyDatabase',
N'SQL Server'
GO

Then you can reference as though they are on the same server, so if the databases are on the same mssql server then skip the above step and just do the following:

[Server name].[database name].[owner].table_name

Wednesday, March 31, 2021
 
Pwner
answered 7 Months ago
62

You can try to use the function I am using in a project I am working on right now... Please see the code below...

function join_table()
{
    $this->db->select(//column name, //column name, //column name);
    $this->db->from(//table1 name);
    $this->db->join(//table2 name, //table1 name.//column name = //table2 name.//column name');
    $this->db->where(//condition);
    return $this->db->get()->result();
}
Wednesday, March 31, 2021
 
redrom
answered 7 Months ago
35

There is a question I ask myself when deciding this that I think will help you as well. The question is: Am I providing a feature to my framework or am I consolidating?

If you have a feature that you are adding to your framework, then you'll want to create a library for that. Form validation, for example, is a feature that you are adding to a framework. Even though you can do form validation without this library, you're creating a standard system for validation which is a feature.

However, there is also a form helper which helps you create the HTML of forms. The big difference from the form validation library is that the form helper isn't creating a new feature, its just a set of related functions that help you write the HTML of forms properly.

Hopefully this differentiation will help you as it has me.

Sunday, August 8, 2021
 
RahulG
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :