Multiple Singleton mysql connections using same class

Question

I have a PHP app that uses 2 different databases. The connection is realized by a singleton mysql class. What I would like to achieve is to use the same class but to connect to another database depending on a variable stated when calling the class. So, the code used (but not yet functional):

/ start the single class
class Singleton {

// declare two private variables
private static $_instace;
private $conn;
var $dbc = 0;

// declare private constructor class
private function __construct() {
    $this->conn = mysql_connect(HOST, USER, PASS);
    //die('DB State: [' . $this->dbc . ']');
    if ($this->dbc) {
       mysql_select_db(DB_DATABASE_PS);
    } else {
       mysql_select_db(DBNAME);
    }
}

// create a singleton method
public static function getconnect($dbc = false) {
    if (!self::$_instace) {
        self::$_instace = new Singleton();   
        $_instace->dbc = $dbc;  
    } else {
        $_instace->dbc = $dbc;  
    }
    return self::$_instace;
}
}

When calling a class that uses the first database (DBNAME), it all works perfect but when trying to use the second database, the code doesn't help me at all.

The code to load the second database inside a new class is made trough this code:

public function __construct() {
   $this->connect = Singleton::getconnect(true);
}

Could someone help me figure it out? Thank you!

Answer 1

The problem is, you're selecting the database during the construction of the object(in __construct() method) and it's completely based on the class member variable $dbc. So the next time you want to change the database, you have to call getconnect() method like this, Singleton::getconnect(true);, and then create another object to change the database, which would eventually defeat the purpose of singleton pattern. So basically, every time you change the database you have to call getconnect() method with true and false parameter alternatively, and create a new object to change the database.

Now comes down to your problem,

how should I use it (the singleton class) to change between the databases, keeping in mind that on a single page there could be data from both databases

Use the following code snippet to implement singleton pattern and change databases using a single object.

class Singleton {

    // declare two private variables
    private static $instance;
    private $conn;

    // Since it's constructor method is private
    // it prevents objects from being created from 
    // outside of the class
    private function __construct() {}

    // It creates an object if not previously created,
    // opens a connection to MySQL Server
    // and returns the object
    public static function getInstance() {
        if(!isset(self::$instance)){
            self::$instance = new Singleton();
            self::$instance->conn = mysql_connect(HOST, USER, PASS);
        }
        return self::$instance;
    }

    // set your database here
    public function setDatabase($db){
        return mysql_select_db($db, $this->conn);
    }

    // Execute your query here
    public function executeQuery($query){
        return mysql_query($query, $this->conn);
    }

    // Close your connection here
    public function closeConnection(){
        mysql_close($this->conn);
    }

}

// Get an instance of Singleton class
$obj = Singleton::getInstance();

// Set database "abc"
if($obj->setDatabase("abc")){
    echo "database has changed <br />";
}else{
    echo "database has not changed <br />";
}

// Display the selected database
$resultset = $obj->executeQuery("SELECT DATABASE() as db");
$row=mysql_fetch_assoc($resultset);
echo $row['db'] . "<br />";

// Set database "def"
if($obj->setDatabase("def")){
    echo "database has changed <br />";
}else{
    echo "database has not changed <br />";
}

// Display the selected database
$resultset = $obj->executeQuery("SELECT DATABASE() as db");
$row=mysql_fetch_assoc($resultset);
echo $row['db'] . "<br />";

// close connection
$obj->closeConnection();

Output:

database has changed
abc
database has changed
def

Sidenote: Please don't use mysql_ database extensions, they were deprecated in PHP 5.5.0 and were removed in PHP 7.0.0. Use mysqli or PDO extensions instead. And this is why you shouldn't use mysql_ functions.