When I first read about mysqli_connect(), I thought we can only perform the sql operations only on the database that was passed as an argument to mysqli_connect() but today I just gave a try that, is it possible to perform join operations on tables of two different databases on my local server. So I have created another database (db_2). Amazingly it worked.
I tried googling on this topic and I have found very old answers related to mysql_connect() and mysql_select_db(). Just take a look at my code
$host="localhost";
$username="root";
$password="";
$db_name="db_1";
$con = mysqli_connect($host,$username,$password,$db_name);
$sql_query = "SELECT name FROM db_1.user as u inner join
db_2.user as us ON us.id = u.id
WHERE u.id = 102 LIMIT 1";
$result = mysqli_query($con,$sql_query);
$row = mysqli_fetch_array($result, MYSQL_ASSOC);
echo $row['name'];
Initially I thought we should make two connections to two databases, something like this to make use of two databases
$con1 = mysqli_connect($host,$username,$password,"db_1");
$con2 = mysqli_connect($host,$username,$password,"db_2");
but only using single connection It is easily able to fetch data between two databases.
My question is is this right way of joining two databases ? I know PDO is better way but out of curiosity just want to know.
Also are there any limitations? Is this prefered way when I have huge data.
Does it really slow down the performance when you grab data between two databases or is it little. Any help is greatly appreciated. Thanks
If both of the databases are on the same server and are accessible by the user you're using. You should be able to put together a query like this...
SELECT db1.table1.column1, db2.table2.column2 FROM db1.table1 JOIN db2.table2 ON db1.table1.column1 = db2.table2.column2;