如何在CodeIgniter中执行我的SQL查询

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

请先访问此链接，以便了解我的SQL语法为何如此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:

主要问题：如何执行我的查询？如果我们在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); ?

如何执行我有多个数据库的查询？我将在这里提供什么[数据库] - >查询（$ SQL）; ？

6 个解决方案

#1

$query = $this->db->query($SQL);

return $query->result_array();

#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:

如果数据库共享服务器，则拥有一个具有两个数据库的privelele的登录名，并且只需运行类似于以下内容的查询：

$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.

否则我认为您可能必须单独运行2个查询并在之后修复逻辑。

#3

I can see what @Þaw mentioned :

我可以看到@Þaw提到的：

$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.

CodeIgniter支持多个数据库。您需要将两个数据库引用保存在单独的变量中，如上所述。到目前为止，你是对的/正确的。

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

请参阅此参考：http：//ellislab.com/codeigniter/user-guide/database/connecting.html

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

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

#6

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

$ this-> db-> select（'id，name，price，author，category，language，ISBN，publish_date'）;

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

#1

$query = $this->db->query($SQL);

return $query->result_array();

#2