合并Codeigniter中2个数据库的结果 [英] Combining results from 2 databases in codeigniter

问题描述

我正在尝试找出如何使用codeigniter在2个mysql数据库中合并来自同一表的结果.

I'm trying to work out how to combine the results from the same table in 2 mysql databases using codeigniter.

两个表都具有相同的列名，但是数据库具有不同的连接设置.

Both tables have the same column names, but the databases have different connection settings.

我目前正在尝试:

public function getAllNames() {

$sql1 = <<SQL
        SELECT 
          id, name, CONCAT(name, ' (', id, ')') AS name2
        FROM
          db1.table
        ORDER BY name
SQL;

$sql2 = <<SQL
        SELECT 
          id, name, CONCAT(name, ' (', id, ')') AS name2
        FROM
          db2.table
        ORDER BY name
SQL;

$q = $this->db->query($sql1." UNION ".$sql2);

return $q->result();

}

将每个数据库的相同查询存储在2个变量中，然后尝试使用UNION合并它们.但是，这是行不通的，因为它无法将数据库名称识别为数据库(而且无论如何也不会以这种方式为每个数据库提供连接设置).我认为ORDER BY在这里也放在错误的位置.我不确定应该为每个查询指定db，还是不确定此方法是否可以正常工作.

Storing the same query for each database in 2 variables then trying to combine them using UNION. However, this can't work because it doesn't recognise the db names as databases (and it wouldn't have the connection settings for each db this way anyway). I think the ORDER BYs are in the wrong place here too. I'm not sure where I should specify the db for each query or if this method would work at all.

有什么想法吗?

推荐答案

尝试一下

$DB1 = $this->load->database('db1');
$DB2 = $this->load->database('db2');

$res1 = $DB1->query($sql1);
$res2 = $DB2->query($sql2);
$result = array_merge($res1 , $res2);

这篇关于合并Codeigniter中2个数据库的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！