+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Union tables in multiple databases (/showthread.php?tid=33935)
Union tables in multiple databases - El Forum - 09-13-2010
[eluser]Harshadewa[/eluser]
Is it possible in CodeIgniter to connect to multiple databases and include tables from 2 or more databases in one query as below?
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
Union tables in multiple databases - El Forum - 09-14-2010
[eluser]danmontgomery[/eluser]
Code:
SELECT E_Name FROM db_a.Employees_Norway
UNION ALL
SELECT E_Name FROM db_b.Employees_USAActive record doesn't support UNION, so you'd just be using $this->db->query()
Union tables in multiple databases - El Forum - 09-25-2010
[eluser]Harshadewa[/eluser]
Quote:Active record doesn’t support UNION, so you’d just be using $this->db->query()
when you say $this->db->query(), its only connecting to 1 database, right?
How can I include 2 databases' tables in one statement like that?
Union tables in multiple databases - El Forum - 03-24-2011
[eluser]Unknown[/eluser]
A UNION is possible, though not directly. Here's a solution I created that uses Active Record but still implements UNION:
$query1 = $this->db->get('Example_Table1');
$join1 = $this->db->last_query();
$query2 = $this->db->get('Example_Table2');
$join2 = $this->db->last_query();
$union_query = $this->db->query($join1.' UNION '.$join2.' ORDER BY column1,column2);
Union tables in multiple databases - El Forum - 03-24-2011
[eluser]danmontgomery[/eluser]
You're executing 3 queries, and the end result is that you still have to use $this->db->query().
[quote author="Harshadewa" date="1285440541"]
Quote:Active record doesn’t support UNION, so you’d just be using $this->db->query()
when you say $this->db->query(), its only connecting to 1 database, right?
How can I include 2 databases' tables in one statement like that?[/quote]
Sorry, I never saw this.
You make a connection to a mysql server, not a database. The database is selected after you make the connection. As long as the user you're connecting with has permission in both databases, you can perform a union on tables across databases using the database.tablename method I described above.