OO PHP - How to create a "generic" database object?

Question

Continuing my journey into more advanced OO, I'm trying to come up with the best way of being able to create a class called database that can happily use the soon-to-be-deprecated mysql_ commands, the mysqli_ library, and perhaps further down the line, other flavours of database as well.

I'd like my calling code to look something like this, for simplicity:

$db = new database("MYSQL", $host,$user,$password,$databaseName); 
$db->query("SELECT myField FROM myTable");

while ($ROW = $db->fetch_assoc($QRY)) {
  echo $ROW['myField'] . "<br/>";
}

When instantiating the database object, thats the part where i'd hope to specify MYSQL, MYSQLI, MSSQL, or whatever I feel like adding later.

I appreciate I could achieve exactly what I want by implementing switch blocks in every method of database:

class database() {

  function __construct($type,$host,$user,$password,$databaseName) { 

    $this->dbType = $type; 

    switch ($type) {

      case "mysql":
        mysql_dao::connect($host,$user,$password,$databaseName);
        break;

      case "mysqli":
        mysqli_dao::connect($host,$user,$password,$databaseName);
        break;

      case "mssql":
        mssql_dao::connect($host,$user,$password,$databaseName);
        break;

      //other cases
    }

  }

  function query($SQL) {  
    switch ($this->dbType) { 
       case "mysql": mysql_dao::query($SQL); break;
       case "mysqli": mysqli_dao::query($SQL); break; 
       case "mssql": mssql_dao::query($SQL); break;
    }
  }

  function fetch_assoc($SQL) {  
    switch ($this->dbType) { 
       case "mysql": mysql_dao::fetch_assoc($SQL); break;
       case "mysqli": mysqli_dao::fetch_assoc($SQL); break; 
       case "mssql": mssql_dao::fetch_assoc($SQL); break;
    }
  }

  //other methods....

}

...But this seems incredibly messy. Every time a method of database was called, the code is checking what type it is and calling the method from a different object.

So my next solution was simply to omit database at all, and just use calling code that looks like this:

$db = new mysql_dao($host,$user,$password,$databaseName); 
$db->query("SELECT myField FROM myTable");

while ($ROW = $db->fetch_assoc($QRY)) {
  echo $ROW['myField'] . "<br/>";
}

...and so we just call the database access object for the type of database we're using, but I don't like that either, if I wanted to shift a whole project from mysql to mysqli, i'd have to seek out all of these object references and change them. Although this is fairly trivial in a modern PHP editor, it still doesn't feel like it should be the best way (i'm starting to get theoretical instead of practical, now!)

So finally, i'd hoped that I could build the database class with a single switch statement:

class database {

    function __construct($type,$host,$user,$pass,$db) {    

        switch ($type) {

            default:
            case "MYSQL":
                return new mysql_dao($host,$user,$pass,$db);
                break;

            case "MYSQLI":
                return new mysqli_dao($host,$user,$pass,$db);
                break;

            case "MSSQL":
                return new mssql_dao($host,$user,$pass,$db);
                break;    
        }    

    }

}

... and then just implement an interface with all of the other methods being in the classes for the individual database types. Unfortunately this isn't working either because even though the constructor for database returns an instance of another object, I can't call database->query because the method doesn't exist in the database object.

As far as I could work out, extending database with mysql_dao / mysqli_dao etc. classes wasn't right either, since you have to call the extended class to use its methods, and I want to call the parent class but use the methods of the child class. So this suggests that database should extend the dao classes, but you can't have one child class with multiple potential parents (can you?)

To conclude - I am almost sure I am missing something, and that what I want to do should be both possible and quite straightforward, and I just haven't thought of the right way - can anyone give me a pointer?

+1 for a good question motivated by “there must be a better way”. I'd be thinking in terms of a factory that returns a proxy object which then delegates to the real object hidden inside, but then again I'm not a PHP programmer. — Donal Fellows
– Donal Fellows, Commented Jul 27, 2011 at 10:46
Also be aware that different databases tend to support different dialects of SQL; this can make switching between them harder than you'd expect. — Donal Fellows
– Donal Fellows, Commented Jul 27, 2011 at 10:48
@Donal this is exactly why I was looking at it the way I am, so later methods like say - selectAllRecords($table,$order) - would be able to write SQL the way the particular database needed it to be written. Although I fear it may not be possible to find a way to remove the need to write ANY SQL outside of the class (when it comes to complex queries), I can definitely get rid of the simple stuff. — Codecraft
– Codecraft, Commented Jul 27, 2011 at 10:59
I should perhaps also point out that even though my examples are simplistic, I like to add in a lot of other functionality (such as query profiling) so its not quite just a case of reinventing the wheel. — Codecraft
– Codecraft, Commented Jul 27, 2011 at 11:16

nickf · Accepted Answer · 2011-07-27 10:48:01Z

4

Despite the fact that what you're doing is reinventing the wheel (take a look at PDO), in general, for cases like this you might want to use something like the factory pattern.

In basic pseudo-code:

$db = Database::create('mysql', $host, ...); // gets a MySQLDatabase object
$db = Database::create('mysqli', $host, ...); // gets a MySQLiDatabase object

answered Jul 27, 2011 at 10:48

nickf

548k199 gold badges660 silver badges727 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Codecraft Over a year ago

I think this wins! Its as I had in the last code block of my question, only I had the return new xxxx in the constructor, that didn't work. Moving them into a create method works a charm!

GordonM · Accepted Answer · 2011-07-27 10:48:27Z

3

Why not just use PDO? It provides a unified way of interfacing with a range of databases (The actual queries it executes have to be written with the target database in mind, but the API is the same regardless of what kind of database you're connecting to).

answered Jul 27, 2011 at 10:48

GordonM

31.9k17 gold badges94 silver badges134 bronze badges

3 Comments

armandomiani Over a year ago

I personally hate user-created libs without needs. Rewriting the wheel.

Codecraft Over a year ago

I'll look into it, but one problem that could occur there is that I don't host everything I write, and so I don't always have access to the PHP config (sometimes I don't even have access to PHP5!)

GordonM Over a year ago

PHP 4 isn't supported any more, if you have customers still using it then I'd be inclined to urge them to upgrade. For their own protection if nothing else, PHP 4 contains vulnerabilities that will never be fixed and using it basically leaves the server it's running wide open to attack.

Pascal MARTIN · Accepted Answer · 2011-07-27 10:45:45Z

0

In this kind of situation, I would generally :

Define some DAOInterface, with methods such as connect, query, fecthAll, ...
- all class that connect to the database will have to implement that interface
- which means that your mysql_dao, mysqli_dao, ... will implement that interface

Then, when working with those classes, the main advantage is :

You'll later instanciate, as a $db variable, either mysql_dao or mysqli_dao, but always get an object that implements DAOInterface
Which means you will be sure that you can invoke all those methods, on your $db object

After that, there will only be one place where you have to choose between mysql_dao and mysqli_dao : it's when you are instanciating one of those two, assigning the result to $db.

Then, as your know for sure that $db is an object that implements DAOInterface, you know for sure that you will always be able to call the same methods on that object.

answered Jul 27, 2011 at 10:45

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

3 Comments

Codecraft Over a year ago

That's one of the solutions I came up with (I just didn't talk about the interface in the original question) but that still means I have to call the DAO directly, and so if I wanted to change, there'd be changes to the calling code in every page that used the database (not accounting for using includes etc).

Pascal MARTIN Over a year ago

There would not be any change anywhere (except where you instanciate the DAO and assign it to $db), as long as all your DAO implement the same interface : the code that uses the methods of that interfaces will remain exactly the same.

Codecraft Over a year ago

I understand that - I was just trying to be even lazier :-) With instancing code always being new database regardless of which actual DAO it used.

David Laberge · Accepted Answer · 2011-07-27 10:53:32Z

0

In order to have the functionality to be functional you will need to pass and return parameter as references as in the following example :

 public function &Query($sql){
     $var = mysql_query($sql, $this->connection);
     return $var;
 }

 public function Fetch(&$cur){
     return mysql_fetch_array($cur);
 }

The "&" represent the reference of the variable not just a copy for more documentation :http://www.php.net/manual/en/language.references.pass.php

What you are trying to do works well. I created on to interface a MySQL database, but I'm sure you'll find a way to create an object for the different type of DB.

answered Jul 27, 2011 at 10:53

David Laberge

16.1k15 gold badges56 silver badges84 bronze badges

Collectives™ on Stack Overflow

OO PHP - How to create a "generic" database object?

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related