Select in 2 databases with Kohana PHP

Question

How to JOIN tables in different databases in Kohana?

$tb_new = 'db_zaboo_feed.feed_' . $feed;
            $ids = DB::query(Database::SELECT, 
                             "SELECT d.fuid_id, d.user_id FROM db_zaboo.displays d
                             LEFT JOIN $tb_new f ON(d.fuid_id = f.uid)
                             WHERE d.user_id = (SELECT user_id FROM $tb_new GROUP BY user_id) AND f.uid IS NULL")->execute();

ERROR:

Database_Exception [ 1146 ]: Table 'db_zaboo.displays' doesn't exist [ SELECT d.fuid_id, d.user_id FROM db_zaboo.displays d LEFT JOIN db_zaboo_feed.feed_3 f ON(d.fuid_id = f.uid) WHERE d.user_id = (SELECT user_id FROM db_zaboo_feed.feed_3 GROUP BY user_id) AND f.uid IS NULL ]

Answer 1

This is not a Kohana specific question. It is a MySQL question.

For operating on multiple databases in the same query, you have to use table names with its database name. Such as:

SELECT * FROM database1.table1 WHERE database1.table1.id IN (SELECT table1_id FROM database2.table2)

Be careful: by literally writing your query, I am not sure if Kohana's query builder prevents SQL injection attacks as it would do by building the SQL query using the query builder's specific functions. I would prevent it using the function mysql_real_escape_string

Answer 2

To use a different database config group pass either the name or the config object to execute().

$result = $query->execute('config_name')

http://kohanaframework.org/3.0/guide/database/query/builder#executing http://kohanaframework.org/3.0/guide/database/config#connection-settings