1

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.

  • Have you looked into how frameworks handle it, eg [Codeigniter](http://ellislab.com/codeigniter), [Cake](http://cakephp.org/), [Symphony](http://symfony.com/), [Wordpress](http://wordpress.org/) etc etc... – Christian Apr 06 '13 at 00:42
  • honestly, i've been trying to find out how wordpress works, and I cannot find the file that connects to the database. If you could tell me it might help. I checked wp-settings and wp-config to no avail. –  Apr 06 '13 at 00:46
  • wp-config.php in the root directory has the DB connection info – Tony Apr 06 '13 at 00:48
  • i know it has the db info. But I can never find something that does an actual query in wordpress. I haven't ever looked too extensively but just click certain files that have them I can't. Idk maybe its a stupid question, but my code really is simple. I could use a framework like zend but I don't 99% of it. So I just figured I would as the best way to use classes and PDO together, because right now I do all of my queries in the class methods –  Apr 06 '13 at 00:54
  • 1
    Refer to this http://stackoverflow.com/questions/7464408/how-do-i-use-a-single-mysql-connection-with-multiple-php-objects and this: http://stackoverflow.com/questions/15715573/php-oop-multiple-classes-require-same-class – egig Apr 06 '13 at 01:54

3 Answers3

6

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Thanks so much. I hear about the apparent 'bad practice' of using singletons. I can't understand why it's a bad practice. I switched up all of my classes to pass the pdo object by reference. –  Apr 06 '13 at 15:45
1

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

Daryl Gill
  • 5,464
  • 9
  • 36
  • 69
  • While I appreciate the time you took on this answer. I'm actually not trying to have multiple db connections but only one. I mean I use PDO so I know it's possible to have two objects of the same class on a page, but it slows it down significantly to connect to the same database twice. –  Apr 06 '13 at 00:55
  • Can you explain how your script is slowing the page down by connecting to the same database? – Daryl Gill Apr 06 '13 at 00:58
  • maybe it's my imagination. But can a page not be slowed down by multiple connections to the same db. because I probably have 3 or 4 per page right now –  Apr 06 '13 at 01:02
  • Connections to the same database? Are you sure you are correctly closing your query prior to making another query on the same database? --- Connecting to different database schemas should not slow down the process – Daryl Gill Apr 06 '13 at 01:04
  • I never make a conscious effort to close a query. should I be? I never use a destructor to set $PDO = null; for example. I thought that was handled by php automatically –  Apr 06 '13 at 01:07
  • 1
    PHP only does what you tell it too.. You should be closing your queries to improve your sites functionality – Daryl Gill Apr 06 '13 at 01:09
  • Okay, I know I should probably ask this as another question. but I think it can be answered easily. How do you close queries? Are you talkign about actual sql queries like "SELECT * FROM tbl" or just database connections? –  Apr 06 '13 at 01:11
  • @Alex the query.. Im not 100% too sure with pdo.. So id suggest look at the manual for "pdo close" – Daryl Gill Apr 06 '13 at 03:01
1

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];
    }
}
Sveta Oksen
  • 401
  • 4
  • 5