php shared db connection ( design pattern help )

Question

I have a small php app that I want to build a layer of db abstraction on top of with a few "model" type classes.

I'm using ezSQL_mysql to do the db work.

My question is what is the best way to design the app? Should I use a singleton pattern to share the db connection? Should my "model" classes extend ezSQL_mysql ? Or maybe I'm totally off base here and need to do something else.

What I need is something like this

Controller.php

   $db = new ezSQL_mysql($db_user, $db_passwd, $db_database, $db_host);


   $user = new User();
   $user->update_email($new_email);

   $sale = new Sale();
   $sale->purchase($amount); 

User_model.php

class User {

   /* uses $db connection */
   function update_email(){
     /* do something */
   };
}

Sale_model.php

class Sale {   
   /* uses $db connection*/

   function purchase () {
    /* do something */ 
   }
}

Answer 1

Okay... so the small app's requirements grew, as per usual and I decided to go with an MVC framework, rather than rolling my own loose set of classes to manage a persistant connection and abstract the database layer.

So now I'm using CodeIgniter http://www.codeigniter.com/ which basically accomplishes what I wanted to do in a easier to manage fashion.

Thanks for the other answers.

Answer 2

Well for small application, definitely use domain object pattern. That is, every object represents db entity.

Then you can decide weather you'll use mapper pattern to add some more complex lookups to db or not.

If you prefer tested solutions, you can check Zend_Db. It covers everything you need, from db adapters, factory, etc. It basically implements gateway pattern, but implementing something like mapper is already shown in reference manual.

Other solutons would include ORM like Doctrine or Propel.

Patterns should not be overused. If it's a small app, solve the problem as easy as possible. If you're doing this as an experiment, try to play with something Doctrine. Those libraries can sometimes be quite amazing.

Answer 3

A DB connection singleton could be problematic. Fortunately, it's not necessary for connection sharing. You can have a connection manager class that, when given a previously used host, user and database name, returns the already open connection.

abstract class ConnectionManager {
    protected $connections = array();
    function connect($host, $db, $user, $pw) {
        if (! isset($this->connections[$host][$db][$user])) {
            $this->connections[$host][$db][$user] = $this->newConnection($host, $db, $user, $pw));
        }
    }
    abstract protected function newConnection($host, $db, $user, $pw);
}

class EzSQLConnectionManager extends ConnectionManager {
    protected function newConnection($host, $db, $user, $pw) {
        return new ezSQL_mysql($user, $passwd, $database, $host);
    }
}

Not addressed here is how to securely store user credentials. Passwords shouldn't be spread willy-nilly throughout scripts, which this approach might lead you to do.