phpmysqlcodeigniterunionmultiple-databases

Combining results from 2 databases in codeigniter


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.

I'm currently trying this:

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

}

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.

Any ideas?


Solution

  • Try like

    $DB1 = $this->load->database('db1');
    $DB2 = $this->load->database('db2');
    
    $res1 = $DB1->query($sql1);
    $res2 = $DB2->query($sql2);
    $result = array_merge($res1 , $res2);