How to execute my SQL query in 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

Please visit first this link so you could understand why my SQL syntax is like this
http://www.x-developer.com/php-scripts/sql-connecting-multiple-databases-in-a-single-query

---

Im 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 that Im having multiple databases? What will I provide here
[database]->query($SQL); ?

Smithy

chk this- #7601528 – Spine

Do you really need two databases for that, might be easier to use two tables? – Blepharitis

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 – Thilda

@Stanyer just following database designed by the company. – Thilda

Answer 1

    $sql="Select * from my_table where 1";    
    $query = $this->db->query($sql);
    return $query->result_array();

Dionnedionysia

This should be avoided at all costs for a few good reasons: 1) It is bypassing the Active Record Pattern (=ARP), Code-Igniter is providing as these are "native" queries. 2) You have to take care of escaping all by yourself (CI does it for you when you use ARP). If you don't, SQL injections are very likely to happen. 3) Caching of query results may not affect them, resulting in more SQL statements and lesser performance. In other words: Rewrite your SQL query into ARP which is the common way with CI 2/3. – Tricornered

$this->db->query($SQL); to $this->db->query($sql); In PHP, variable and constant names are case sensitive – Yurt

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

Answer 2

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.

Timeout

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. – Thilda

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. – Timeout

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

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. – Timeout

Answer 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

Eldenelder

Answer 4

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();

Recreate

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

Answer 5

 return $this->db->select('(CASE 
            enter code hereWHEN orderdetails.ProductID = 0   THEN dealmaster.deal_name
            WHEN orderdetails.DealID = 0 THEN products.name
            END) as product_name')

Asur

Answer 6

$this->db->select('id, name, price, author, category, language, ISBN, publish_date');

       $this->db->from('tbl_books');

Libnah

Answer 7

Select Query:
$this->db->select('column1, column2');
$this->db->from('your_table');
$query = $this->db->get();
Insert Query:
$data = array('column1' => 'value1','column2' => 'value2');
$this->db->insert('your_table', $data);
Update Query:
$data = array('column1' => 'new_value1','column2' => 'new_value2');
$this->db->where('your_condition');
$this->db->update('your_table', $data);
Delete Query:
$this->db->where('your_condition');
$this->db->delete('your_table');

Primeval