« VLC UnRAR plugin
SQL backed preference system for PHP »
Aug 01 2008

Generic Object Oriented SQL interface for PHP

Posted by fli in General, tags: ,

This is an extensible object oriented wrapper around the MySQL and MSSQL (Microsoft SQL Server) interfaces provided by PHP. I’ve been using them for a while, it’s nothing new and no rocket science but others might find them useful.


Why do this at all? why just not stick with the API provided by PHP? It’s really nothing else than a more convenient way to interact with a SQL server and exposing it’s interface in a vendor natural way. Instead of writing mysql_query everywhere, one simply writes query.

Having a consistent interface will obviously help if you are using multiple types of database servers or someday would like to migrate to another SQL server (of course, weird and vendor specific SQL code could be a problem in this case). Other advantages are things like automatic disconnection on object destruction etc.

The foundation is an abstract class called SQLConnector and provides the basic interface. The class will connect to the database on creation and disconnect when the object is destroyed, it also implements __sleep and __wakeup so that it’s possible to serialize a SQL connection.

Because SQLConnector is abstract one can’t create objects directly from it, we’ll create child classes for each type of SQL-server we want to connect to. These objects will share the same interface and can be used interchangeably.

File db_iface.inc

<php
abstract class SQLConnector
{
    private $m_dbAddress; /* Address of SQL server */
    private $m_dbAccount; /* Account name (username) */
    private $m_dbPwd;     /* Password */
    private $m_dbName;    /* Initial database */
 
    /* Base constructor */
    public function __construct($address, $account, $pwd, $name) {
        $this->m_dbAddress = $address;
        $this->m_dbAccount = $account;
        $this->m_dbPwd = $pwd;
        $this->m_dbName = $name;
 
        /* Call the connect method in the child class */
        $this->connect($this->m_dbAddress, $this->m_dbAccount,
            $this->m_dbPwd, $this->m_dbName);
    }
 
    /* Let the child disconnect when the object is destroyed */
    public function __destruct() {
        $this->disconnect();
    }
 
    /* We are about to be serialized, disconnect
     * and return data needed for serialization
     */
    public function __sleep() {
        $this->disconnect();
        return array('m_dbAddress', 'm_dbAccount',
            'm_dbPwd', 'm_dbName');
    }
 
    /* We have been unserialized, re-connect */
    public function __wakeup() {
        $this->connect($this->m_dbAddress, $this->m_dbAccount,
            $this->m_dbPwd, $this->m_dbName);
    }
 
    /*
     * Abstract methods that needs to be implemented by child classes
     */
    abstract protected function connect($address, $account,
        $pwd, $name);
    abstract protected function disconnect();
    abstract function query($query);
    abstract function fetchRow();
    abstract function fetchAll();
    abstract function getNumRows();
    abstract function freeResult();
    abstract function getError();
}
?>

The following class implements MySQL connectivity using the interface above

File: db_mysql.inc

<?php
 
require_once('db_iface.inc');
 
class MySQLConnector extends SQLConnector
{
    private $m_dbHandle;
    private $m_Result;
 
    /* 
     * Connect function, also selects correct database
     * Returns 1 upon success, otherwise 0
     */
    function connect($address, $account, $pwd, $name) {
        $this->m_dbHandle = @mysql_connect($address, $account, $pwd);
 
        if ($this->m_dbHandle != 0) {
            if (mysql_select_db($name, $this->m_dbHandle)) {
                return 1;
            }
            else {
                return 0;
            }
        }
        else {
            return 0;
        }
    }
 
    /*
     * Disconnect function
     * Returns 1 upon success, otherwise 0
     */
    function disconnect() {
        if (@mysql_close($this->m_dbHandle) != 0) {
            return 1;
        }
        else {
 
            return 0;
        }
    }
 
    /*
     * Does a mysql-query, returns 1 upon sucess otherwise 0
     */
    function query($query) {
        $this->m_Result = mysql_query($query, $this->m_dbHandle);
 
        if ($this->m_Result != 0) {
            return 1;
        }
        else {
            return 0;
        }
    }
 
    /*
     * Fetches an array row
     */
    function fetchRow() {
        return mysql_fetch_array($this->m_Result);
    }
 
    function fetchAll() {
        while ($row = mysql_fetch_array($this->m_Result)) {
            $a_rs[] = $row;
        }
        mysql_free_result($this->m_Result);
        return $a_rs;
    }
    /*
     * Get number of rows
     */
    function getNumRows() {
        return mysql_num_rows($this->m_Result);
    }
 
    /*
     * Free resources allocated by a query 
     */
    function freeResult() {
        mysql_free_result($this->m_Result);
    }
 
    /*
     * Get errorstring
     */
    function getError() {
        return mysql_error($this->m_dbHandle);
    }
}

A (very) similar class for MSSQL connectivity
File: db_mssql.inc

<?php
require_once('db_iface.inc');
 
class MSSQLConnector extends SQLConnector
{
    private $m_dbHandle;
    private $m_Result;
 
    /*
     * Connect function, also selects correct database
     * Returns 1 upon success, otherwise 0
     */
    function connect($address, $account, $pwd, $name) {
        $this->m_dbHandle = @mssql_connect($address, $account, $pwd);
 
        if ($this->m_dbHandle != 0) {
            if (mssql_select_db($name, $this->m_dbHandle)) {
                return 1;
            }
            else {
                return 0;
            }
        }
        else {
            return 0;
        }
    }
 
    /*
     * Disconnect function
     * Returns 1 upon success, otherwise 0
     */
    function disconnect() {
        if (@mssql_close($this->m_dbHandle) != 0) {
            return 1;
        }
        else {
            return 0;
        }
    }
    /*
     * Does a mssql-query, returns 1 upon success otherwise 0
     */
    function query($query) {
        $this->m_Result = mssql_query($query, $this->m_dbHandle);
 
        if ($this->m_Result != 0) {
            return 1;
        }
        else {
            return 0;
        }
    }
 
    /*
     * Fetches an array row
     */
    function fetchRow()
    {
        return mssql_fetch_array($this->m_Result);
    }
 
    function fetchAll()
    {
        while ($row = mssql_fetch_array($this->m_Result)) {
            $a_rs[] = $row;
        }
        mssql_free_result($this->m_Result);
        return $a_rs;
    }
 
    /*
     * Get number of rows
     */
    function getNumRows()
    {
        return mssql_num_rows($this->m_Result);
    }
 
    /*
     * Free resources allocated by a query
     */
    function freeResult()
    {
        mssql_free_result($this->m_Result);
    }
    /*
     * Get errorstring
     */
    function getError()
    {
        return mssql_error($this->m_dbHandle);
    }
}
?>

So, how do one use this then? It’s quite simple

require_once('db_mysql.inc');
require_once('db_mssql.inc');
 
$c1 = new MySQLConnector('mysql-host', 'username', 'password', 'mydb');
$c2 = new MSSQLConnector('mssql-host', 'username', 'password', 'mydb');
 
$c1->query("select something from somewhere");
while ($row = $c1->fetchRow()) {
 ...
}
 
$c2->query("select somethingelse from somewhereelse");

Improvements, oh yes. The connect functions should really throw an error if things doesn’t work.
Also, wrappers for any SQL server should be quite easy to implement.

If this article was usable or otherwise helpful for you, tell a friend or put a link back from your own website/blog.

This entry was posted on Friday, August 1st, 2008 at 5:19 pm and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Generic Object Oriented SQL interface for PHP”
  1. Al Almeida says:
    August 28, 2009 at 5:26 am

    hey there the concept is interesting, could give some examples on how would you display the results of queries for Select, Update, Delete using the MSSQL Interface? also do you have one example on the use of stored procedures on MSSQL server?
    Thanks

  2.  
Leave a Reply