SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,740
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My DB connection Script Creating Two Connections to Same DB?

    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:

    PHP Code:
    $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:

    PHP Code:
    $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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    There are a few ways to avoid duplicates, here's a singleton that does this.

    Code PHP:
    <?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.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,740
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    //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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •