Definitively, there is much to be gained from adopting an object oriented approach to use MySQL in different projects. While we find a plenty of room on the Web for well-structured MySQL abstraction classes, such as PEAR::DB to name a frequently used one, we’ll gain a practical grounding writing our own version, even if we’re quite far from getting that level of complexity.

Anyone who has spent a few days playing around with PHP, knows how to connect to MySQL and fetch some data, using a procedural approach. Yes, probably you’ve seen this code hundreds of times:

// connect to MySQL
$db=@mysql_connect('host','username','password') or die('Error connecting to the MySQL server');
// select database
@mysql_select_db('database',$db) or die('Error selecting database');
// perform a query selecting some records
$result=mysql_query('SELECT * FROM table',$db) or die('Error performing query');
// display results
while($row=mysql_fetch_array($result,MYSQL_ASSOC)){
    // code to display results
}

In the above snippet we’ve connected to the MySQL server, selected a database, performing a simple “SELECT” statement to retrieve some records, finally displaying the results. All of the process was performed using the native PHP functions, taking a procedural approximation. I’m not going to complain loudly about these lines, since I’ve found myself, many times in the past, writing this code over and over again. However, if we’re honest, this approach doesn’t scale well for larger applications, even if we go one step further wrapping up the whole script into a function. A much better solution would be developing a class, which hides all of the internal processing to connect, fetch data, process results, and so forth, focusing primarily on the data handled, rather than on the functions themselves.

Keeping that idea in mind, let’s create a class to performs all of the most common operations associated to MySQL. This newly developed class structure, which I’ve denominated “MySQLConnector”, looks like this:

class MySQLConnector {
    // data member declaration
    var $conId; // connection identifier
    var $host; // MySQL host
    var $user; // MySQL username
    var $password; // MySQL password
    var $database; // MySQL database
    var $result; // result set
    function MySQLConnecto($host,$user,$password,$database){
        // code to setup  connection parameters
        $this->connectDB();
    }
    // connect to MYSQL server and select database
    function connectDB(){
        // code to connect to the server and select database
    }
    // perform query
    function performQuery($query){
        // code to perform query
    }
    // fetch row
    function fetchRow(){
        // code to fetch a row
    }
}

The above listed class, presents a few simple methods to deal with the usual tasks, often involved when working with MySQL. The constructor takes the same parameters used in the procedural solution, that is, the host to connect $host, the username $user, the password $password, and finally the database to work with, that is $database.

A quick look at the class shows us an immediate benefit, since we’re using one single method (the constructor) for connecting to the server and select the proper database. As you remember, the procedural solution takes up two lines of code.

The rest of the methods speaks for themselves. This way, we have a “connectDB()” private method that connect to the database, then a “performQuery()” method to perform a query against the database, and lastly, a “fetchRow()” method, which retrieves a row at a time from a result set. Definitively, the methods are very understandable, don’t you think so?