two database connection: php + mysql

Question

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!

Yaniro · Accepted Answer · 2011-12-25 10:12:06Z

3

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.

answered Dec 25, 2011 at 10:12

Yaniro

1,5879 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Damien Pirsy Over a year ago

@SašoKrajnc don't post code in comments, it gets difficult to read. Edit your question instead and add it there

Sašo Krajnc Over a year ago

A PHP Error was encountered Severity: NoticeMessage: Use of undefined constant link1 - assumed 'link1'Filename: result.phpLine Number: 211 A PHP Error was encountered Severity: WarningMessage: mysql_query(): supplied argument is not a valid MySQL-Link resourceFilename: result.phpLine Number: 211 A PHP Error was encountered Severity: WarningMessage: mysql_fetch_array(): supplied argument is not a valid MySQL result resourceFilename:result.phpLine Number: 213

Yaniro Over a year ago

Please note that you forgot the '$' in front of the $link variable when you passed it to mysql_query

mario · Accepted Answer · 2011-12-25 10:04:20Z

0

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

answered Dec 25, 2011 at 10:04

mario

146k20 gold badges243 silver badges293 bronze badges

1 Comment

mario Over a year ago

Your syntax is wrong. Also don't post passwords. For code use backticks. But don't post lengthy code into comments anyway, use the edit link on your question.

Collectives™ on Stack Overflow

two database connection: php + mysql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related