up vote 0 down vote favorite

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!

share|improve this question

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
What doesn't work? are you getting an error message? – Yaniro Dec 25 '11 at 11:53
@SašoKrajnc don't post code in comments, it gets difficult to read. Edit your question instead and add it there – Damien Pirsy Dec 25 '11 at 12:14
ok ... I post the changed code ;) – Sašo Krajnc Dec 25 '11 at 12:22
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 – Sašo Krajnc Dec 25 '11 at 13:05
Please note that you forgot the '$' in front of the $link variable when you passed it to mysql_query – Yaniro Dec 25 '11 at 13:14
show 1 more comment
up vote 0 down vote

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
share|improve this answer
sorry...I don't know how to get comment into code=) – Sašo Krajnc Dec 25 '11 at 11:26
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. – mario Dec 25 '11 at 11:27
Thank you...It is working now;) – Sašo Krajnc Dec 25 '11 at 13:30

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.