Join tables from multiple databases with different user credentials

Question

i have a need to join two tables from different databases.
code will look just like this:

$tbl1 = 'table1';
$tbl2 = 'table2';

$DB1 = 'DB1';
$DB2 = 'DB2';

$connect1 = mysql_connect(DB_SERVER,DB_USER1,DB_PASSWORD1);
mysql_select_db ($DB1, $connect1);

$connect2 = mysql_connect(DB_SERVER,DB_USER2,DB_PASSWORD2);
mysql_select_db ($DB2, $connect2);
/* note both the DB are in the same server and users has privileges to theyr own datadases say usr1 to DB1, usr2 to DB2 */

mysql_query("SELECT * FROM $DB1.$tbl1 AS tb1 LEFT JOIN $DB2.$tbl2 AS tb2 ON tb1.id = tb2.id", $connect1);

The above query returns a error saying table2 is not present and it is obvious because I'm using $connect1 .

Answer 1

If each one of your two users only has access to one database and not the other, you will not be able to use both databases/tables in one query -- as no user will be able to run that query.

You'll either have to :

• Get a user who has access to both databases -- and, as such, can run this query,
• Or fetch data from each database/table with the user who has access to it -- and then work on those data from your PHP code to merge them (basically, doing the join in PHP, and not in SQL ; even if that's probably not the most efficient idea).