CODEWITHSUNDEEP
phpdatabase
Pankaj Khurana
Pankaj Khurana

Reputation: 3271

Connecting to two databases

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

Upvotes: 0

Views: 303

Answers (6)

symcbean
symcbean

Reputation: 48357

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 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;

Upvotes: 1

Pankaj Khurana
Pankaj Khurana

Reputation: 3271

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));

Upvotes: 0

Bobby
Bobby

Reputation: 11576

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

Upvotes: 0

Brent Baisley
Brent Baisley

Reputation: 12721

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?

Upvotes: 0

middus
middus

Reputation: 9121

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.

Upvotes: 2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798646

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.

Upvotes: 0

Related Questions