PHP OOP database design

Question

OK, this is just a quick question and I might take some slack for this but I'm just looking for a little guidance as I am completely self taught. I do a lot of reading and try to do a lot of building--I'd say I'm coming into a nice intermediate stage of php, mysql, and web knowledge in general--by no means advanced or overly confident--still learning.

I'm really trying to tackle OOP in PHP and so I want to create a good lean database wrapper for MySQL, just MySQL, I'm most comfortable with MySQL and I dont see any reason to use any other database. I dont want to create any sort of portability in the design--I want it to be specific to my database; so I dont want to use PDO.

So the question I have as of right now in the beginning is should I create a class that EXTENDS mysqli and then have create model classes for my database tables that extend that base database class? so class->child = mysqli->DbBase->UsersModel ? This would require a lot of $this statements inside the class, would it not?

Or should I instantiate a mysqli class and pass it to DbBase?

Answer 1

Classes kind of represent things in the real world (or even imaginary "things"), right? An instance of a DB represents a connection to that db. Has a model something in common with a database connection? Not really. I would suggest to include instances of your database class in those models classes you're going to write, because a model uses a database connection to access it's data but is not a kind of database connection.

Concerning Mysqli <-> DBClass: That really depends on what you're trying to achieve with that DBClass - does it extend Mysqli with some extra functions or anything? If it doesn't, don't use inheritance there, otherwise you can use it.

---

A very basic example, just to give you the idea: (it is actually a simplified but definitely not complete version of the ActiveRecord pattern)

abstract class DbTable {
    /* An instance of your DBClass (=Database Connection), to be used if no
     * other connection is specified. */
    protected static $_defaultDbAdapter = null;

    /* The db connection to be used by this instance. */
    protected $_dbAdapter = null;

    /* The name of the table in the database. */
    protected $_tableName = '';

    public static function setDefaultDbAdapter(DbClass $db) {
        self::$_defaultDbAdapter = $db;
    }

    public function setDbAdapter(DbClass $db) {
        $this->_dbAdapter = $db;
    }

    public function getDbAdapter() {
        if (null === $this->_dbAdapter) {
            $this->setDbAdapter(self::$_defaultDbAdapter);
        }
        return $this->_dbAdapter;
    }

    public function insert(array $data) { /*...*/ }
    public function update(array $data, $where) { /*...*/ }
    public function delete($where) { /*...*/ }
    public function select($where) { /* may e.g. return an array of DbTableRow childclass instances */ } 

    // ...
}

class Users extend DbTable {
    protected $_tableName = 'my_users_table';
}

abstract class DbTableRow {
    /* The row itself (may be not yet saved to the db!) */
    protected $_data = array();

    /* The row as it is in the database (to find differences, when calling save()). */
    protected $_cleanData = array();

    /* An instance of the table that this row belongs to. */
    protected $_table = null;

    public function __construct(DbTable $table, array $data = array()) { /*...*/ }
    public function save() { /* uses $this->_table->insert()/update() */ }
    public function __get($key) { /*...*/ }
    public function __set($key, $value) { /*...*/ }
    // ...
}

class User extends DbTableRow { }

Usage:

// Make a new connection to the database
$db = new DbClass('...'); // or whatever you name that class...

// Set this connection to be the default connection
DbTable::setDefaultDbAdapter($db);

// Create a new user
$users = new Users();
$user = new User($users);
$user->email = 'test@example.com';
$user->save();

Answer 2

If you really want to learn and understand OOP then I think you should start to learn some PHP frameworks (like Zend Framework) and read it's source codes. I learned lots of things from them.

Answer 3

If you're going to work with OOP, I'd strongly recommend using PDO, because it's the most updated and OO implementation of the MySQL library. I don't think PDO-MySQL is any less MySQL-specific than MySQLi.

In any case, you shouldn't extend PHP's class in this case, you should keep an object with the database connection as a property of your class.

You should also look into the Singleton design pattern, which is really useful in these cases. Take a look at this post from today: Move out mysql connection into another class