How to execute a SELECT query on one database containing a WHERE IN subquery to be executed on another database using CodeIgniter?

Question

I have a problem with my query and I need to join two tables from different databases now my problem is how can I execute my query. I got my syntax format from here

I'm using CodeIgniter and here is an idea of what my query looks like:
Notice the way I'm selecting my columns: DATABASE_NAME.TABLE_NAME.COLUMN_NAME

$ENROLLEES = $this->load->database('ENROLLEES', TRUE);
$ACCOUNTS  = $this->load->database('ACCOUNTS', TRUE);

$SELECT    = "SELECT $ACCOUNTS.BALANCES_TABLE.IDNO, $ACCOUNTS.BALANCES_TABLE.balance";
$FROM      = "FROM $ACCOUNTS.BALANCES_TABLE";
$WHERE     = "$ACCOUNTS.BALANCES_TABLE.IDNO IN (SELECT $ENROLLEES.ENROLLEES_TABLE.IDNO FROM $ENROLLEES.ENROLLEES_TABLE)";

$SQL       = $SELECT . " " . $FROM . " " . $WHERE;

MAIN PROBLEM: How to execute my query?
If we do like this in CodeIgniter:

$ENROLLEES->query($SQL); or $ACCOUNTS->query($SQL);

How can I execute my query involving multiple databases? What will I write here:
[database]->query($SQL);?

Do you really need two databases for that, might be easier to use two tables? — Ryan
– Ryan, Commented May 8, 2013 at 7:57
sir @SureshKamrushi you didnt get my question, I know how to define to databases in CI , I am trying to join 2 tables from 2 different databases, what will I provide in CI's syntax your_database->query(SQL); ? since I am querying from two databases — Þaw
– Þaw, Commented May 8, 2013 at 7:59

Robin Castlin · Accepted Answer · 2013-05-08 08:33:18Z

13

If the databases share server, have a login that has priveleges to both of the databases, and simply have a query run similiar to:

$query = $this->db->query("
SELECT t1.*, t2.id
FROM `database1`.`table1` AS t1, `database2`.`table2` AS t2
");

Otherwise I think you might have to run the 2 queries separately and fix the logic afterwards.

answered May 8, 2013 at 8:33

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Þaw Over a year ago

I have 3 databases loaded in my config.php file. I have these $ENROLLEES = $this->load->database('ENROLLEES', TRUE); $ACCOUNTS = $this->load->database('ACCOUNTS', TRUE); obviously the last is the default. if I use $this->db->query() It would mean that Im using the default database.

Robin Castlin Over a year ago

You would only require one connection, that is one "load to database". If the user you load with has access to all given databases in this case. And when fetching tables, you can specify the database.table.

Þaw Over a year ago

do you mean sir that I would not load my 2 other databases and just use my default?

Robin Castlin Over a year ago

You would have one database as default, and the user whom you connect with would have PRIVELEGES to the other 2 databases aswell, and then you can nest the queries between databases as I've done in given example.

Nah · Accepted Answer · 2013-08-16 13:19:10Z

3

I can see what @Þaw mentioned :

$ENROLLEES = $this->load->database('ENROLLEES', TRUE);
$ACCOUNTS = $this->load->database('ACCOUNTS', TRUE);

CodeIgniter supports multiple databases. You need to keep both database reference in separate variable as you did above. So far you are right/correct.

Next you need to use them as below:

$ENROLLEES->query();
$ENROLLEES->result();

and

$ACCOUNTS->query();
$ACCOUNTS->result();

Instead of using

$this->db->query();
$this->db->result();

See this for reference: http://ellislab.com/codeigniter/user-guide/database/connecting.html

answered Aug 16, 2013 at 13:19

Nah

1,7683 gold badges27 silver badges47 bronze badges

1 Comment

mickmackusa Apr 26 at 9:59

Are you suggesting that this task cannot be performed in one go?

Gurpreet Singh · Accepted Answer · 2018-02-17 09:03:17Z

1

http://www.bsourcecode.com/codeigniter/codeigniter-select-query/

$query = $this->db->query("select * from tbl_user");

OR

$query = $this->db->select("*");
            $this->db->from('table_name');
            $query=$this->db->get();

answered Feb 17, 2018 at 9:03

Gurpreet Singh

3792 silver badges6 bronze badges

1 Comment

mickmackusa Over a year ago

This answer seems to be masterfully avoiding the context of the asked question where the actual task is about querying two different databases.

Collectives™ on Stack Overflow

How to execute a SELECT query on one database containing a WHERE IN subquery to be executed on another database using CodeIgniter?

3 Answers 3

4 Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Linked

Related