CODEWITHSUNDEEP
phpclasssingletondatabase-connection
dangelsaurus
dangelsaurus

Reputation: 7522

database connections in PHP classes

I need some advice\suggestions on how to create\handle database connections for a project I'm working on. I'm creating a simple work order system for my company using PHP 5.4.3, right now there isn't any type of authentication as I'm not ready for that complexity. So far I'm planning on the following structure

<? php
class Db {
    private static $dbh = null;

    static function open_connection() {
        self::$dbh = new PDO(... }

    static function query($sql) {
        $result = self::$dbh->... }

    static function fetch_array($result){
        ...}
...

}

class Workorder extends Db {

    protected static $table_name = ...
    ...

    public $wonum;  
    ...

    function instantiate {
    //return workorder objects
    ...}

    function findallwos {
    //find all work orders...
    ...}

...}

I think this will work fine for pages that I need to display all of the work orders or put in new work orders. But I have a few pages that require very simple queries, for example one of the reporting pages will just have a drop down list of the 3 technicians we have working for us, if I was using a global $dbh variable I could just do 

function create_dropdown () {
    GLOBAL $dbh;

    $sql = "select...";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $arry = $sth->fetchAll(PDO::FETCH_ASSOC);
    .....

But I'd rather stay away from global variables as eventually I would like to add future complexity to this site which might not work with globals. (Including multiple users with authentication etc). So my questions are:

Should I not use the Db class as I have it designed, and instead use some sort of connection_factory class instead that passes out connections? (and not have my workorder class be an extension of anything)

Should I just use static calls to the Db class to pull in simple queries? db::fetch_array...

Or do you guys have any other suggestions? I've come across a lot of different ways to go about this, but ideally I'd like to design this so that I don't have to completely recode everything when I get to the point where I'm adding multiple users with different permissions etc.

Upvotes: 1

Views: 306

Answers (1)

Moe
Moe

Reputation: 2730

What I personally would criticise on the code design you provided is that you don't separate concerns and that there is no abstraction. Don't make the huge mistake so many did before to mix SQL statements, the type of presentation and your business logic all over your code. At some point you might want to switch the database product, create a mobile version, add functionality ... and then you need to change dozens of files.

To prevent these things in general, I would suggest an approach like this:

  • Create a clean and lightweight model of the entities and their relationships and don't care about persistence (i.e. database), access control, etc. at this point (e.g. a simple class Workorder that contains classes of Employee, ...)
  • Write interfaces that define the necessary persistence operations on your model (i.e. saveWorkorder(Workorder $workorder), getAllWorkorders(), loadWorkorder(), ...). In your code, refer only to these interfaces and the methods they provide and never to a concrete implementation
  • Now, implement the classes of the persistence interfaces with a certain DB. Don't forget to encapsulate the fundamental DB actions in a class as well as you will use them in many persistence classes.

I hope my answer is not too vague, but software design is a very wide field and it's hard to cover all aspects in one single answer. And as with all questions concerning software design, there's no correct or wrong solution as it depends a lot on the concrete project, the requirements, on personal coding style, etc...

On a side note, if you plan to do more projects of this kind, it probably would pay off to invest the time to get known with a framework (like Zend Framework, Symphony, CakePHP and many more) as they provide you with a lot of existing functionality that you can use out of the box, like database connectors, separation of views and business logic, ...

Upvotes: 1

Related Questions