PHP - mySQL DB class

Question

I've seen how powerful OOP is in c++ so I started reading up on OOP for PHP. This made me want to revamp one of my sites so it would use OOP PHP to clean up the royal mess that it's in now. I already know the basic concepts of OOP, but I'm struggling a bit with applying it.

So far, all the sites I've seen that use OOP PHP have some sort of mySQL DB class. I can definitely see its benefits (cleaner code when you want to access something in the DB) but I'm not entirely sure how to set mine up. Here's what I'm wondering:

• What are the first things I should do when building this class from the
  ground up? (How should this class be set up? What's the idea behind that structure? What does it look like when set up like that?)
• What kinds of methods should be in my DB class? (What kind of functionality should my DB class offer? SQL Inserts? SQL Updates? plain ole' SQL queries? What data should these methods return?)
• How do I determine when I need to add a new method to the class?
• How should I use those methods in my other code?

Thanks.

Answer 1

There are several ways to go about this, firstly you can "integrate" a system into a native system such as mysqli or you can go about creating your own system / structure from scratch using drivers.

With the first method your a little limited as you have to abide by the internal structure of the native API.

My personal approach is usually the second as i can create a driver based system and decide what native system to use depending on the host operating system and several other factors.

When creating a database API for your applications to run on you do not want it to be specifically for that application, you should be able to grab the libraries drop them into a new project and everything should work as expected, so making it abstract is the first issue we will have to get around.

The next obstacle is to make the code extendible, the programming world changes radically so we should be prepared for changes, although the code would only suffice for a few years, it would be beneficial if we thought about thus years and was prepared.

imagine if next week a new database layer was created that was 50x faster then the current mysql(i) layer that's integrated into PHP, you would want to switch to that api ASAP, thinking about these factors is always a good thought to have during the development stages.

Firstly we should decide on the structure, with traditional abstraction layers the code is usually all built into 1 large class file, this can be bad programming as it should be broken down into its purposes.

We should be separating the classes / objects so that they perform the task there specifically designed for.

We should have 1 global "controller" which would be the main Database Class, this would control all operations sent to and from the sub classes.

We would be designing for PHP 5.3 as minimum, so lets get started:

What i tend to do is create some pseudo code of what the end-result would be like, this way i will have an understanding of what should be implemented.

$Connection = new DatabaseConnectionDetails(array(
    "hostname" => "localhost",
    "username" => "root",
    "password" => "n/a",
    "charset" => "UTF-8",
    "database" => "my_database",
    "driver" => "MySQL",
));

$Database = Database::getInstnace($Connection);

$Query = $Database->Query("SELECT * FROM {?:table} WHERE id = {?:id}",array(
    "table" => "posts",
    "id"    => 22
));

if($Query->Execute())
{
    $Results = $Query->GetResults();

    foreach($Results as $Result)
    {
         echo $Result->data; //trims, converts to int, returns;
    }
}

This would be how I would like to connect to my database, so this is how I would start working with the base class

class Database
{
    private static $_DBInstance;

    public static function getInstance(IDatabaseConnectionDetails $ConnectionDetails)
    {
        if(self::$_DBInstance === null)
        {
            self::$_DBInstance = new Database($ConnectionDetails);
        }
        return self::$_DBInstance;
    }

    public function __construct(IDatabaseConnectionDetails $ConnectionDetails)
    {
        //We will come back to this.
    }
}

Looking at the class above you will see that firstly it has a create instance (singleton) that would help in using the database throughout any application, you would also note that im specifically defining what parameters are required.

Keeping the above factors in place throughout the whole abstraction layer will make life much easier.

Let me just explain a little about the the directory structure, you would have to seperate the following class

• Database
  • Query Classes
• Interfaces
• Drivers
• Helpers

and I would go for a structure a little like so:

• includes
  • DBAL
    • Database.class.php
    • DatabaseConnectionDetails.class.php
    • Drivers
    • Interfaces
    • Result

As stated above would require a IDatabaseConnectionDetails interface, the purpose of this is so that the separation of credentials and the main database are made, therefore the IDatabaseConnectionDetails has a primary role of looking after the users credentials.

class DatabaseConnectionDetails implements IDatabaseConnectionDetails
{
    public $hostname = "localhost";
    public $username = "";
    public $password = "";
    public $charset = "UTF-8";
    public $driver = "mysqli";

    public function __construct($params)
    {
        //Set Params here
    }
}

Now the beauty of creating a class for your configuration was mentioned above, the extensibility of the class, for example:

class MyCustomConfig extends DatabaseConnectionDetails
{
        public $hostname = "host.domain.tld";
        public $username = "my_other_user";
        public $password = "IJH87b&OTIT8fh";
        public $driver   = "mssql";
}

This way as the classes are interfaced you can send any class into the main database object, being custom or not as the interface allows you to be extensible.

Now the drivers that are required, you should note that each driver should be part of an driver interface, that forces a set of methods to be within the driver itself.

interface IDatabaseDriver
{
    public function setConnectionDetails(IDatabaseConnectionDetails $ConnectionDetails);
    public function connect();
    public function escape($string);
    public function query($string);
    /*
       all the methods here that will allow the main class can access, such as:
       - next()
       - prev()
       - reset()
       ect ect
    */
}

The main database class would then be able to load the correct driver depending on $Config->driver.

you would create generic functions in the Main Database Class that would mimic a typical database system such as fetchObject,fetchArray() etc, these methods would then use the driver object to select, prepare, sanitize the data and then return the results using another object/class such as QueryResult which would have a set a dedicated methods that are solely for the purpose of traversing a result set.

Hope you enjoyed this long read but should get you started with an idea in your head.

Regards: Robert

Answer 2

If you mean a class to connect to the database, prepare and run queries, use PDO (try "Writing MySQL Scripts with PHP and PDO" for a tutorial). If you mean a Data Access Object (DAO), which is a class (or classes) to function as a bridge between the database records and your application objects, try the data mapper or active record patterns. Note that the active record pattern is a little older and falling out of favor with some, so the articles about active record in PHP tend to be older and are a bit out of date.

Whatever pattern you apply, the four basic operations your class(es) should provide are create, read, update and delete (CRUD). Create and read return application objects, and update and delete can return values indicating success or failure.

Using the class(es) is fairly straightforward: whenever you need an object, use create or read rather than the object constructor. When to use update or delete depends on your business logic. For example, you may need to update whenever you're done with an object (i.e. when the object is destroyed). What's trickier is how an object or function gets a reference to DAOs. Reading Martin Fowler's article on dependency injection for a few different approaches.

Further reading:

• Persistence Patterns
• Practical PHP Patterns: Data Mapper
• Implementing the Data Mapper Design Pattern in PHP 5
• Implementing The Domain Model: Entries and Authors
• Using the Active Record Pattern with PHP and MySQL