My DB connection Script Creating Two Connections to Same DB?

PHP
1

I have to remote mysql servers. One that writes/reads and one that just reads. To balance the load, I randomly choose which database to pull data from with this script:


$updater=mysql_connect ("xxx.196", "user", "pw") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db",$updater);

$rand = rand(1,2);

if($rand == 1) {
$reader=mysql_connect ("xxx.200", "user", "pw") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db",$reader);
$myserver = "Mysql2";
} else {
//same DB as the first one listed on top
$reader=mysql_connect ("xxx.196", "user", "pw") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db",$reader);
$myserver = "Mysql1";
}

Since all my updates/inserts/deletes only go to one of the server, I have $updater connection. But then i have to randomly select one of the two mysql servers. If $updater is selected again, am I now double-connecting to the $updater database? Which results to something like:


$updater=mysql_connect ("xxx.196", "user", "pw") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db",$updater);

$reader=mysql_connect ("xxx.196", "user", "pw") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db",$reader);

Am I creating two connections because of this? If so, how can I better write this to avoid this outcome?

Thanks
Ryan

2

There are a few ways to avoid duplicates, here’s a singleton that does this.


<?php
class DatabaseFactory
{
    const
        READ        = 0,
        WRITE        = 1,
        RANDOM    = 2;


    protected static
        $instances = array(
            DatabaseFactory::READ        => null,
            DatabaseFactory::WRITE    => null
        );
        
    protected static
        $config = array(
            DatabaseFactory::READ => array(
                'host'        => '192.168.0.2',
                'user'        => 'username',
                'pass'        => 'password',
                'schema'    => 'database'
            ),
            DatabaseFactory::WRITE => array(
                'host'        => '192.168.0.1',
                'user'        => 'username',
                'pass'        => 'password',
                'schema'    => 'database'
            )
        );
        
    public static function build($type = DatabaseFactory::READ)
    {
        if(false === array_key_exists($type, self::$instances))
        {
            throw new Exception('Invalid Database type');
        }
        
        if(DatabaseFactory::RANDOM === $type)
        {
            $type = array_rand(self::$instances);
        }
        
        if(null === self::$instances[$type])
        {
            $config = self::$config[$type];
            self::$instances[$type] = mysql_connect($config['host'], $config['user'], $config['pass']);
            mysql_select_db($config['database'], self::$instances[$type]);
        }
        
        return self::$instances[$type];
    }
}


$read        = DatabaseFactory::build(DatabaseFactory::READ);
$write    = DatabaseFactory::build(DatabaseFactory::WRITE);
$random    = DatabaseFactory::build(DatabaseFactory::RANDOM);

It’s untested, but should convey its intent. :slight_smile:

3

Wow,

So are $read $write $random the resource link identifiers?

I’m just wondering what then I’d have my scripts call on my content pages, such as:


//example
include "connection.php"; //where the connection scripts are

$getinfo = mysql_query("Select ....", $reader);

$updateinfo = mysql_query("Update....", $updater);

I’m trying to see how the scripts that are already on my content pages would apply to this.

Thanks!
Ryan