CODEWITHSUNDEEP
phpmysqldatabasedatabase-connection
Sašo Krajnc
Sašo Krajnc

Reputation: 133

two database connection: php + mysql

I have two databases in same server with same username and pass. Right now I am connecting to only one database, but I would like to connect to both.

For now this is my code, whick connect only to one database:

connect1.php

<?
$servername='localhost';

$dbusername='user';
$dbpassword='pass';

$dbname1='db1';
$dbname2='db2';

$link1 = connecttodb($servername,$dbname1,$dbusername,$dbpassword);
$link2 = connecttodb($servername,$dbname2,$dbusername,$dbpassword);

function connecttodb($servername,$dbname,$dbusername,$dbpassword)
{
    $link=mysql_connect ("$servername","$dbusername","$dbpassword",TRUE);
    if(!$link){die("Could not connect to MySQL");}
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
    return $link;
}
    ?>

I display result in result.php with this code:

<?
require "connect1.php"; 

$q=mysql_query("select * from table1 where username='test' order by id",link1);

while($nt=mysql_fetch_array($q)){
echo "$nt[location]";
}

?>

I would like to display similar data in result.php, but with connection to db2

How can I do that? Thank you!

Upvotes: 0

Views: 14409

Answers (2)

Yaniro
Yaniro

Reputation: 1587

Instead of having a single global $link variable, you need two:

$link1 = connecttodb($servername1,$dbname1,$dbusername1,$dbpassword1);
$link2 = connecttodb($servername2,$dbname2,$dbusername2,$dbpassword2);

And of course change connectodb() to:

function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
    $link=mysql_connect ("$servername","$dbuser","$dbpassword",TRUE);
    if(!$link){die("Could not connect to MySQL");}
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
    return $link;
}

Please note that i've added a fourth parameter to mysql_connect, stating TRUE for the new_link parameter (this will only be needed if the two databases reside on the same server).

Then, for each query you will have to specify the corresponding link variable (either $link1 or $link2) according to the database you wish to query.

Upvotes: 3

mario
mario

Reputation: 145512

You need to connect a second time, and this is crucial, keep the new handle in $link2. So you first need to add a new connect function, and I would advise creating the global variables via reference parameter now:

function connecttodb(&$link, $servername,$dbname,$dbuser,$dbpassword)
{
   $link=mysql_connect ("$servername","$dbuser","$dbpassword");
   if(!$link){die("Could not connect to MySQL");}
   mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}

connecttodb($link1, $servername, $dbname, $dbusername, $dbpassword);
connecttodb($link2, $servername2, $dbname, $dbusername, $dbpassword);

You then need to carry around your $link1 and $link2 variables for the actualy queries:

$q = mysql_query("select * from table1 where username='test' order by id ", $link1);

Then do the same query again for $link2. And at this point it might be best to investigage some loops for querying multiple databases, or a class which abstracts it away (if you generally query against two sources anyway).

Actually there is also a super-lazy option, if both tables are equivalent and running on the same server, just different database names. You could then append the query using UNION ALL:

 select * from table1 where username='test' order by id
 UNION ALL
 select * from db2.table1 where username='test' order by id

Upvotes: 0

Related Questions