Accessing multiple databases at the same time

[SCook]

Hi all,

 

Is there a good way to be able to open a connection to more than one db at a time?  I'm doing something that will need it's own db, but I also need a connection to the first one which contains member info, etc. 

 

Any suggestions would be great, thanks.

[redarrow]

<?php
    include('adodb.inc.php');     # load code common to ADOdb
    $conn1 = &ADONewConnection('mysql');  # create a mysql connection
    $conn2 = &ADONewConnection('oracle');  # create a oracle connection

    $conn1->PConnect($server, $userid, $password, $database);
    $conn2->PConnect(false, $ora_userid, $ora_pwd, $oraname);

    $conn1->Execute('insert ...');
    $conn2->Execute('update ...');
?>

[SCook]

I assume adodb.inc.php is a standard include?  I'm not familiar with it.  What I use to connect is the following:

 

access = mysql_connect(host, user, pw);]

mysql_select_db(db, access);

 

So along that vane, is there a way to have two connections at once?

[papaface]

$con1 = mysql_connect("localhost","username","pw") or die (mysql_error());
$con2 = mysql_connect("localhost","username","pw") or die (mysql_error());

mysql_select_db(db, $con1) or die (mysql_error());//will run on connection 1
mysql_select_db(db, $con2) or die (mysql_error());//will run on connection 2

mysql_query("SOME SQL",$con1) or die (mysql_error());//will run on connection 1
mysql_query("SOME SQL",$con2) or die (mysql_error());//will run on connection 2

[Barand]

If the 2 databases are on the same server then you only need a single connection to that server, assuming you have access privileges to both using the same username

 

To access a second database on the server you only need to prepend the tablename with the database name

 

<?php
$con = mysql_connect($host, $user, $pwd);
mysql_select_db (main_db);

$sql1 = "SELECT * FROM mytable";                                     // from main db

$sql2 = "SELECT m.id, m.name FROM other_db.members m";    // from other db

$sql3 = "SELECT m.name, a.col1
            FROM mytable a
            INNER JOIN other_db.members m ON a.member = m.id";          // from both