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
– klaustopher, Commented Jun 4, 2012 at 14:33
possible duplicate of How do you connect to multiple MySQL databases on a single webpage? — George Cummins
– George Cummins, Commented Jun 4, 2012 at 14:34
See the above duplicate, and remember to set 'true' as the fourth parameter of your second mysql_connect statement. — George Cummins
– George Cummins, Commented Jun 4, 2012 at 14:35
Btw: You should switch to PDO or the mysqli_* functions (see: de.php.net/manual/en/mysqlinfo.api.choosing.php) — klaustopher
– klaustopher, Commented Jun 4, 2012 at 14:36

Elhussein Hashem · Accepted Answer · 2012-06-04 15:35:37Z

1

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

answered Jun 4, 2012 at 15:35

Elhussein Hashem

1061 silver badge4 bronze badges

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

Collectives™ on Stack Overflow

Using multiple mysql connections in 1 class

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related