Using multiple mysql connections in 1 class

Question

I am writing a cronjob which would transfer data from one table, process it and then apply it to a web application. I have 2 seperate database connections for this. At the moment, the connection credentials are the same except it's different databases, but going live, it will be entirely seperated.

The issue is I am creating a connection then assigning it in my class to $this->staging and the other is called $this->production. I use mysql_ping to ensure that both connections are working.

What appears to happen is that when it does a query, it disregards the connection identifier ($this->staging example) and tries to do a query on the last of the 2 connections created.

I use the connection identifier within the query:

 // Connection:

 $this->staging = $this->mysql($config['staging']);
 $this->production = $this->mysql($config['production']);

 $sql = "SELECT * FROM TABLE";
 $query = mysql_query($sql,$this->staging);

 // Returns an unknown table in database defined in $this->production.

I return the mysql_connect as the $this->mysql() method, and not the mysql_select_db(). If I try returning mysql_select_db() mysql_ping does not work.

Here is the mysql() method:

// connect(): Connect to a database (MySQL):

private function mysql($cred) {
    $connect = mysql_connect($cred['server'],$cred['username'],$cred['password']);
    if($connect) {
        $db = mysql_select_db($cred['database']);
        if($db) {
            return $connect;
        } else {
            $this->informer("[FATAL]","Could not make a mysql database connection on server ".$_SERVER['SERVER_ADDR']." for database: ".$cred['database']);
        }
    } else {
        $this->informer("[FATAL]","The database credentials appears to be wrong. ".$_SERVER['SERVER_ADDR']." for the mysql database: ".$cred['database']);
    }
}

How does your mysql(..) function look like? Does it use mysql_select_db on the right connection? — klaustopher, Jun 04 '12 at 14:33
possible duplicate of [How do you connect to multiple MySQL databases on a single webpage?](http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage) — George Cummins, Jun 04 '12 at 14:34
See the above duplicate, and remember to set 'true' as the fourth parameter of your second `mysql_connect` statement. — George Cummins, Jun 04 '12 at 14:35
Btw: You should switch to PDO or the mysqli_* functions (see: http://de.php.net/manual/en/mysqlinfo.api.choosing.php) — klaustopher, Jun 04 '12 at 14:36

score 1 · Accepted Answer · answered Jun 04 '12 at 15:35

The problem happens because you are connecting to the same MySql server, when you connect to the staging (second connection) the PHP returns the same connection created for the production and don't create a new one, to force the PHP to create a new one you should change the "mysql_connect" call to be like this:

 $connect = mysql_connect($cred['server'],$cred['username'],$cred['password'], true);

this will force the PHP to start a new connection.

Ref: http://www.php.net/mysql_connect

Using multiple mysql connections in 1 class

1 Answers1