CODEWITHSUNDEEP
phpmysqlooppdo
user1928545
user1928545

Reputation:

Multiple Database Connections PHP

I'm having trouble figuring out a good solution to this problem. I have multiples classes on my site that all handle different things. I have a comment class, a profile class, a login class and so on and so forth. I;m happy with my code design so far because basically all the logic is out of my html template. So I just have very minimal logic in my template. The only problem is, I have a single class for a database connection, and I extend every class from the database class and use the parent constructor to connect to the database. The only problem is every page has at least 2 separate classes on it and now that the site is getting more complicated and I'm having 4 or 5 classes per page, it's noticeably effecting the load time since i have to reconnect to the database each time.

My question is what's the best way to use database connections in a class. I'm thinking maybe database queries will have to be handled in the html, or I could create a whole function file that is included on every page that has a database connection loaded with it. But I'd like to know how more experienced programmers do it so I can fix it the right way.

Thanks for the advice.

Upvotes: 1

Views: 9412

Answers (3)

Sveta Oksen
Sveta Oksen

Reputation: 401

I think that using a sinlgeton or global is the best for this situation. It is considered a bad practice to use singletons and globals for the reason that they are easily misused. But in this case, the benefits overcome the disadvantages.

Additionally, it is a common practice over the open source platforms to use singleton or global for DB connections: - Singleton is used in Magento for getting/setting the PDO object - Global is used in Wordpress in order to maintain the resource returned by mysql_connect function.

You can avoid the class dependency, by adding a factory behavior to the connector. Thus, I suggest you to write something like this:

class DBconnector
{
    private static $_connections = array();

    private static createConnection($type)
    {
        switch($type){
            case 'pdo':
            default:
                return new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');
        }
    }

    public static getConnection($type)
    {
        if(!isset(self::_connections[$type])){
            self::_connections[$type] = self::createConnection($type);
        }
        return self::_connections[$type];
    }
}

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157839

Although you could use a singleton to be sure that only one connection is used, it seems that a holy war is declared on Stack Overflow to this honest pattern.

Nevertheless, extending application classes from DB class is a bad idea. These classes have nothing in common. Database class is just a service - so, use it as a service. Some of your classes have to send emails too for sure - don't you extend your classes from email class too?

You have to instantiate your DB class once, and then pass this object to other classes in their constructors. The easiest way would be to use just global $db;, or you can bother passing them in constructor parameters. Or use that widely advertized dependency injection approach.

Anyway, you should use single connection (with same credentials) throughout whole script execution. Opening and closing connection many times is no better than having multiple simultaneous connections.

Upvotes: 6

Daryl Gill
Daryl Gill

Reputation: 5524

If using MySQli: 

$Con_1 = new mysqli ("host","user","password","database");
$Con_2 = new mysqli ("host","user","password","database");
$Con_3 = new mysqli ("host","user","password","database");

Then interact with: 

$PreparedStatement_1 = $Con_1->prepare(); // Connect to database instance specified in $con_1
$PreparedStatement_1->bindparam('',);
$PreparedStatement_1->execute();
$PreparedStatemet_1->close(); // close to free up other statements
$PreparedStatement_2 = $Con_2->prepare(); // Connect to database instance specified in $con_2
$PreparedStatement_2->bindparam('',);
$PreparedStatement_2->execute();
$PreparedStatemet_2->close(); // close to free up other statements

If using PDOMysql 

$Con_1 = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');
$Con_2 = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');
$Con_3 = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');

Examples are more or less the same with the MySQli Examples.

Not advised, By mysql

$Con_1 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$Con_2 = mysql_connect('localhost', 'mysql_user', 'mysql_password');

Interact with: 

$Query = mysql_query("",$Con_1);
$Query2 = mysql_query("",$Con_2);

If you wish to hand code, you may do so.. But multiple database instances can be done with PHP, and there are frameworks available:

Codeigniter, CakePHP, Symphony, Wordpress

Upvotes: 1

Related Questions