Connecting to two databases

Question

Hi,

I have an application in which I want to authenticate a user from a first database & manage other activities from another database. I have created two classes. An object of the classes is defined in a file:

$objdb1=new db1(),$objdb2=new db2();

But when I try to call $objdb1->fn(). It searches from the $objdb2 & is showing table1 doesnot exists?

My first file database.php

class database
{
private $hostname;
private $database;
private $username;
private $password;
private $dblinkid;

function __construct()
{
    if($_SERVER['SERVER_NAME'] == 'localhost')
    {
        $this->hostname = "localhost";
        $this->database = "aaaa";
        $this->username = "xxx";
        $this->password = "";
    }
    else
    {
        $this->hostname = "localhost";
        $this->database = "xxx";
        $this->username = "xxx";
        $this->password = "xxx";
    }
    $this->dblinkid = $this->connect();
}

       protected function connect()
{
    $linkid = mysql_connect($this->hostname, $this->username, $this->password) or die("Could not Connect ".mysql_errno($linkid));
    mysql_select_db($this->database, $linkid) or die("Could not select database ".mysql_errno($linkid)) ;
    return $linkid;
}

Similarly second file

class database2
{
private $vhostname;
private $vdatabase;
private $vusername;
private $vpassword;
private $vdblinkid;

function __construct()
{
    if($_SERVER['SERVER_NAME'] == 'localhost')
    {
        $this->vhostname = "xxx";
        $this->vdatabase = "bbbb";
        $this->vusername = "xxx";
        $this->vpassword = "";
    }
    else
    {
        $this->vhostname = "localhost";
        $this->vdatabase = "xxxx";
        $this->vusername = "xxxx";
        $this->vpassword = "xxxx";
    }
    $this->vdblinkid = $this->vconnect();
}

        protected function vconnect()
{
    $vlinkid = mysql_connect($this->vhostname, $this->vusername, $this->vpassword) or die("Could not Connect ".mysql_errno($vlinkid));
    mysql_select_db($this->vdatabase, $vlinkid) or die("Could not select database ".mysql_errno($vlinkid)) ;
    return $vlinkid;
}

Third file

$objdb1 = new database();
$objdb2 = new database2();

Can you help me on this?

Regards,

Pankaj

Answer 1

Most likely your class does not pass the appropriate connection resource to the database functions. The second argument to e.g. mysql_query() is the connection resource. Simply store this resource in an instance variable on connection, and use it every time you do something with the database.

Answer 2

Without knowing your classes, it is difficult to help. If you are using PDO, I can guarantee you that you can create multiple instances connected to different databases without any problem. If you are using the mysql_ family of functions you probably just forgot to set the link_identifier parameter (see here).

However, having a class db1 and a class db2 sounds like a code smell to me. You probably want to have two instances of the same class with different attributes.

Answer 3

Each time you call mysql_connect() or the equivalent mysqli functions, if a connection already exists using those same credentials it gets reused - so anything you do to modify the state of the connection, including changing database, charsets, or other mysql session variables affects "both" connections.

Since you are using the mysql_connect() function you have the option to force a new connection each time (RTFM) but this is not supported on all the extensions (IIRC mysqli and PDO don't alow for this).

However IMHO this is the wrong way to solve the problem. It just becomes messy trying to keep track of what's connected where.

The right way would be to specify the database in every query:

 SELECT stuff FROM aaaa.first f, aaaa.second s
 WHERE f.something=s.something;

C.

Answer 4

Your problem may be in checking if the SERVER_NAME is "localhost". Seems like you may be using the same connection strings in both classes. What is $_SERVER['SERVER_NAME'] resolving to?

Answer 5

You're looking for the fourth parameter of mysql_connect(), which states that it shouldn't reuse existing connections:

$dbLink1 = mysql_connect($server, $user, $pass, true);
mysql_select_db($db1, $dbLink1);

$dbLink2 = mysql_connect($server, $user, $pass, true);
mysql_select_db($db2, $dbLink2);

mysql_query("SELECT * FROM table1", $dbLink1); // <-- Will work
mysql_query("SELECT * FROM table1", $dbLink2); // <-- Will fail, because table1 doesn't exists in $db2

Answer 6

Hi,

Passing the fourth parameter as true to mysql_connect resolves the issue.

 $linkid = mysql_connect($this->hostname, $this->username, $this->password,true) or die("Could not Connect ".mysql_errno($linkid));