CODEWITHSUNDEEP
phpdatabase-design
user701273
user701273

Reputation: 41

configure php app with multiple database connections

I have a php page with several databases. I keep reading that multiple databases is bad and you should combine them all into one, but I want to keep things separate for better organization.

So let's say I stay with multiple databases. Is that bad? Now I'm seeing problem with mysql_max_connections. So I'm looking into singleton methods for getting connections. But the singleton classes seem to be only for one database connection. Is that the case? Do I need to create a separate singleton class for each database?

Clarification: I anticipate the databases getting fairly large. I already have several hundred tables on one database and more are added with new content. I have heard that you don't want more then several thousand tables in any particular database. Maybe the best answer is to get everything combined somehow, but it would take a major overhaul so I would like to stick with multiple databases. I just want to know what is the most efficient way to organize connections for multiple databases.

Upvotes: 1

Views: 1305

Answers (3)

gd1
gd1

Reputation: 11403

I'm not sure I understood the question, what you mean for "so I'm looking into singleton methods for getting connections", I know what a singleton is but I really don't see what you are planning to do. More, I don't see how mysql_max_connections is related to all of this, it has quite nothing to do with what you ask.

I may suggest you to create a common interface to all the databases, like a wrapping class named "DB" (as a singleton, if you want), and define some methods to query the databases so that the callee doesn't have to "know" what DB to get the data from.

But all of this seems pointless...

EDIT: (see comments, also)

1) To significantly reduce the number of connections, use mysql_pconnect(). But read the docs, carefully, I know there are some concerns.

2) Generic code. It'll take a bunch of work, but it's worth. I do this way:

  • I create a query repository, with a bunch of queries everyone named with a sensitive caption and some SQL code like "SELECT foo, bar FROM foobar WHERE bar = '?'
  • I create a DB class with a function named query (or something like that), that accepts the query name, the query parameters as an array, and transparently builds the acual query string, executing it on the appropriate database for that query type!
  • The function returns some handle to an object that can iterate through the query results, pretty like mysql_fetch_array, but better.

If in the future you'll drop mysql for anything else, it'll work without changing callee code. The same if you merge the two databases.

EDIT (2): uh oh, it seems I've finally understood what you ask. Your databases (or better, schemata) are more than one, but the DBMS is just a single instance of MySQL running on localhost? If it's like this you can simply edit your queries. "SELECT * FROM database.table WHERE ..."

Upvotes: 0

dev-null-dweller
dev-null-dweller

Reputation: 29482

Typical singleton looks like:

class Singleton{
    private static $instance;
    private function __construct(){}
    private function __clone(){}
    public function Instance(){
        if(!self::$instance){
            self::$instance = new self;
        }
        return self::$instance;
    }
}

But you can extend it by having multiple instances but slightly different from each other:

class MultiSingleton{
    private static $instance = array();
    private function __construct(){}
    private function __clone(){}
    public function Instance($index = 'default'){
        if(!isset(self::$instance[$index])){
            self::$instance[$index] = new MultiSingleton($index);
        }
        return self::$instance[$index];
    }
}

That way you can have one class to rule them all ;) and still follow singleton pattern

Upvotes: 1

pharno
pharno

Reputation: 46

using multiple databases is fairly easy. mysql_connect() returns a Resource value, which Identifies the connection. You can add that value to, for example, mysql_query():

$db1 = mysql_connect("localhost");    
$db2 = mysql_connect("google.com");

$query1 = mysql_query("select ....", $db1);
$query2 = mysql_query("select ....", $db2);

Upvotes: 0

Related Questions