How can I connect to two databases

Question

i am trying to connect to two databases to create a search engine for a couple of my databases. Heres a test code. can someone tell me what i am doing wrong or if it is possible. thanks.

mysql_connect("localhost","user","pass");
mysql_select_db("db1");
mysql_select_db("db2");

$search=mysql_query("SELECT * from db1.repairs, db2.order from db1,db2");
while($row=mysql_fetch_array($search)){
    echo $row['first_name']."&nbsp;".$row['esn']."&nbsp;".$row['order_type']."<br>";
}

If your question has been resolved please accept one of the answers and upvote all of the answers that were useful (including the accepted answer). If you need any additional clarification feel free to comment on the answers. — jcbwlkr, Apr 05 '13 at 13:59

score 1 · Answer 1 · answered Apr 04 '13 at 19:51

You can query across databases if you specify the database name before the table name like this

SELECT     a.col1, b.col2 
FROM       db1.table1 AS a
INNER JOIN db2.table2 AS b ON a.someIdFromA = b.someIdFromB

As Korcholis mentions the problem is in your select. Also you do not want to use the mysql_* functions if you can avoid it. PDO or MySqli are preferred.

Edit At least this works using MySQL. I would bet it works for most other RDBMSes as well, but I don't have others handy to test and I can't say if this conforms to SQL standards or not. Comments anyone?

I like this but I didn't test it, maybe, I'm not willing, for it isn't the proper way.. — 5ervant - techintel.github.io, Apr 30 '13 at 05:28

score 1 · Answer 2 · answered Apr 04 '13 at 19:57

You can use

<?php
$db1 = mysql_connect("localhost","user","pass");
$db2 = mysql_connect("remote","user","pass");

mysql_select_db("db1", $db1);
mysql_select_db("db1", $db2);

$query1 = mysql_query("USE somedatabase", $db1);
$query2 = mysql_query("USE otherdatabase", $db2);

Or try with a class that handles these connections in a different instances http://www.joni2back.com.ar/programacion/php-class-for-mysql-databases/

score 0 · Answer 3 · edited May 23 '17 at 12:13

mysql_connect returns a $resource. You can connect twice and select a database with each one (in fact, you can select a database from the connect itself), and then use each connection.

However, your problem is that your SELECT is incorrect. You are trying to select fields from tables from databases, which is not correct. In fact, you cannot fetch two different databases in a so fancy way, because they are considered two sets of information independent and unrelated between them. That's why tables exist, to fit that problem.

This other answer, however, may have a solution.

Otherwise, you could connect to each database using two mysql_connect and two resources, fetch the values, and cross them yourself. Not the best option, I know, but an answer that could fit your needs.

PS: If you are beginning the project right now, switch to Mysqli or PDO. Mysql is deprecated.

5ervant - techintel.github.io · Answer 4 · 2013-04-04T20:21:24.037

Try to review this, and maybe you can't query a database with querying FROM:

<?php
$con1 = mysqli_connect("$hostname", "$user1", "$password1", "$db1");
if (mysqli_connect_errno($con1)) {
    echo mysqli_connect_error();
}

$con2 = mysqli_connect("$hostname", "$user2", "$password2", "$db2");
if (mysqli_connect_errno($con2)) {
    echo mysqli_connect_error();
}

$search1 = mysqli_query($con1, "SELECT * from $db1table");

$search2 = mysqli_query($con2, "SELECT * from $db2table");

/* Other PHP codes here */

mysqli_close($con1);
mysqli_close($con2);
?>

You can even improve this code, nor minimized it!

How can I connect to two databases

4 Answers4