Codeigniter querying multiple databases in same query

[eluser]ivantcholakov[/eluser]
1. I did not check before whether on MySQL level joining between two tables from different databases is possible. Well, it is possible - http://stackoverflow.com/questions/56983...-databases. So the example I've given is valid in this regard.

2. You have to make a connection to one of your databases, which is the main for your application. And when you join to a table from the other database, as I can see, you use fully qualified table/field names. Then, you have two options to implement the query:

2.1. To pass the query directly to the database server - http://ellislab.com/codeigniter/user-gui...eries.html. I this case, if there are some parameters by that you modify the query, you have to manually make proper escaping to avoid SQL injections - see "Escaping Queries" on the same page. If you use CodeIgniter 2.x version, this is the way for such a complex query.

2.2. On CodeIgniter 3.0-dev, for your example you may use the so called "query builder" (the former "active record" feature, just the name changes) - http://ellislab.com/codeigniter/user-gui...ecord.html. In this approach, by using a series of API-calls you instruct the corresponding library to create the SQL query for you, that suits to your database server. Escaping parameters (if there are any) is done automatically by the query builder.

I demonstrated the second way, which I prefer. This is the main reason for me to switch to CodeIgniter 3.0-dev, the query builder there is improved much, it can build real-life complex queries like in your example.