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

chk this- http://stackoverflow.com/questions/7601028/using-multiple-databases-within-codeigniter — Suresh Kamrushi, May 08 '13 at 07:55
Do you really need two databases for that, might be easier to use two tables? — Ryan, May 08 '13 at 07: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, May 08 '13 at 07:59

score 42 · Answer 1 · edited Mar 06 '23 at 13:55

42

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

edited Mar 06 '23 at 13:55

Gausul

265
3
16

answered Apr 07 '15 at 11:58

Mohit maru

817
8
15

3

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. – Roland Sep 19 '17 at 10:10
1

$this->db->query($SQL); to $this->db->query($sql); In PHP, variable and constant names are case sensitive – H.M Maruf May 24 '21 at 11:43

score 13 · Answer 2 · answered May 08 '13 at 08:33

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 08 '13 at 08:33

Robin Castlin

10,956
1
28
44

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. – Þaw May 08 '13 at 08:37
1

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`. – Robin Castlin May 08 '13 at 08:43
do you mean sir that I would not load my 2 other databases and just use my default? – Þaw May 08 '13 at 08:48
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. – Robin Castlin May 08 '13 at 11:44

score 3 · Answer 3 · answered Aug 16 '13 at 13:19

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

score 3 · Answer 4 · answered Feb 17 '18 at 09:03

3

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 '18 at 09:03

Gurpreet Singh

367
2
6

score 0 · Answer 5 · answered Dec 30 '17 at 20:31

0

 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')

answered Dec 30 '17 at 20:31

Muhammad Sulman

1,611
1
19
25

score 0 · Answer 6 · answered Jan 18 '18 at 11:41

0

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

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

answered Jan 18 '18 at 11:41

How to execute my SQL query in CodeIgniter

6 Answers6

Linked

Related