Geek for Brains

Writing your own classes can be very satisfying. You expand your programming knowledge, provide yourself with a useful tool and learn about object oriented programming all in one.

Today I’m going to show you how to write your own MySQL class in PHP 5. We’ll be using the standard mysql_connect and mysql_query functions to ensure compatibility across multiple configurations.

The Breakdown

Like any experienced programmer will tell you, you should come up with a battle plan before jumping into code. As tedious as it may seem, it undoubtedly saves you time and frustration in the long run.

Object variables

A variety of object variables will be required to hold information while we work with our database.

1. $dbConn: Database connection link
2. $dbSql: Store the loaded SQL statement
3. $dbQuery: Store reference to last query
4. $dbBind: Stores bound hooks and values

The connect() Method

The connect method will be used to connect to our MySQL database.

The prep() Method

The prep function will be used to load SQL statements into the object and store them in an object variable.

The bind() Method

As with most queries, you’ll need to pass some parameters within your SQL statement. That’s the role of the bind method. The bind method provides a way to inject values into your SQL statement via the use of hooks. Bound values should automatically be cleaned to avoid SQL injection attacks. We’ll cover this in more depth later.

The run() Method

The run function does as you may expect; it runs the SQL statement we loaded with the prep method. The run function will return the number of rows affected by the statement.

Helper Methods

The helper methods will perform common tasks such as get all results, get only one row or count the number of affected rows.

The Core Class

Now that we have an outline of what our class looks like, lets get coding. First we’ll create the class core. In this example, our class name will be MyDB, but you can make it anything you want.

class MyDB {
 
    private $dbConn;             // Database connection link
    private $dbSql;              // Store the loaded SQL statement
    private $dbQuery;            // Store reference to last query
    private $dbBind = array();   // Stores bound hooks and values
 
}

The connect() Method

Ok, so we have our class core. Lets add the connection method to link in our database. Add the following method to your class file.

public function connect($dbHost, $dbName, $dbUser, $dbPass)
{
    $this->dbConn = mysql_connect(
        $dbHost,
        $dbUser,
        $dbPass
    ) or die(mysql_error());
    mysql_select_db($dbName, $this->dbConn) or die(mysql_error());
}

For simplicity sake, I’ve used die functions to handle errors. Another approach would be to use PHP 5’s Exception handler.

The prep() Method

As we stated in the class break down, the prep() method will be used to load our SQL statement into an object variable. Add the following method to your class.

public function prep($sql)
{
    $this->dbSql = $sql;
}

A simple but important method.

The bind() Method

The bind method is where our class starts getting juicy. We’ll be using this method to load values into our SQL statements via the use of hooks.

The first parameter defines the hook to look for, and the second parameter defines the value to replace the hook with. Values loaded with the bind method should automatically be escaped to avoid SQL injection attacks.

Add the following method to your class file.

public function bind($hook, $value)
{
    $this->dbBind[$hook] = $this->escape($value);
}

As you can see, we’re replacing the hook ($hook) with the value ($value). We’re also using a method we haven’t created yet, called escape(). This method will be used to fix any malformed SQL strings. We’ll be creating the escape() method next.

The escape() Method

As stated above, the escape method will be used to fix any malformed SQL strings, and prevent possible SQL injection attacks. The method will be private as its only used by other methods from within the our class.

private function escape($value)
{
    if(get_magic_quotes_gpc())
        $value = stripslashes($value);
    return mysql_real_escape_string($value, $this->dbConn);
}

The run() Method

Now that we have all the pre-steps to our query, lets complete it with the run method. This method will run the SQL string loaded with the prep method, and assign the returned query reference to our $dbQuery object variable. The run method will also return the number of rows affected by the query.

public function run()
{
    $sql = $this->dbSql;
    if(is_array($this->dbBind))
        foreach($this->dbBind as $hook => $value)
            $sql = str_replace($hook, "'" . $value . "'", $sql);
    $this->dbQuery = mysql_query($sql) or die(mysql_error());
    $this->dbBind = array();
    return $this->numRows();
}

As you can see the query is run while any errors are handled with the die function. Any values set with our bind method are loaded into the SQL statement. A method we haven’t created is being used as well. The numRows() method is used to determine the number of rows affected by the last query. We’ll be creating this and other “helper” methods next.

Helper Methods

The helper methods will be used to perform common tasks on query results. These include fetching all rows, fetching a single row and returning the number of affected rows.

public function fetchAll($type = MYSQL_ASSOC)
{
    $tmpArr = array();
    while($row = mysql_fetch_array($this->dbQuery, $type))
    {
        $tmpArr[] = $row;
    }
    return $tmpArr;
}
 
public function fetchAssoc()
{
    return mysql_fetch_assoc($this->dbQuery);
}
 
public function numRows()
{
    return mysql_num_rows($this->dbQuery);
}
 
public function insertId()
{
    return mysql_insert_id($this->dbConn);
}

Usage

Phew, we’re done. Lets take this baby for a whirl. First we create our SQL statement and load it with the prep method. Then we’ll bind some values using the bind method and run it. We’ll also get the query results using some of our helper methods.

// Load our class and instantiate
require('MyDB.php');
$MyDB = new MyDB();
 
// Make the connection
$MyDB->connect('localhost', 'demo_database', 'demo_user', 'demo_password');
 
// Load SQL statment into object
$MyDB->prep('SELECT * FROM demo_table WHERE id = :id');
 
// Bind a value to our :id hook
// Produces: SELECT * FROM demo_table WHERE id = '23'
$MyDB->bind(':id', 23);
 
// Run the query
$MyDB->run();
 
// Get some results
$row = $MyDB->fetchAssoc();
$rows = $MyDB->fetchAll();
$count = $MyDB->numRows();

The Finished Class

class MyDB {
 
    private $dbConn;             // Database connection link
    private $dbSql;              // Store the loaded SQL statement
    private $dbQuery;            // Store reference to last query
    private $dbBind = array();   // Stores bound hooks and values
 
    /**
     * Used to strip slashes and escape strings
     */
    private function escape($value)
    {
        if(get_magic_quotes_gpc())
            $value = stripslashes($value);
        return mysql_real_escape_string($value, $this->dbConn);
    }
 
    /**
     * Handles connection to the database.
     * Die functions are used to catch any errors.
     */
    public function connect($dbHost, $dbName, $dbUser, $dbPass)
    {
        $this->dbConn = mysql_connect(
            $dbHost,
            $dbUser,
            $dbPass
        ) or die(mysql_error());
        mysql_select_db($dbName, $this->dbConn) or die(mysql_error());
    }
 
    /**
     * Loads a raw SQL string into the object $dbSql variable
     */
    public function prep($sql)
    {
        $this->dbSql = $sql;
    }
 
    /**
     * Load bound hooks and values into object variable
     */
    public function bind($hook, $value)
    {
        $this->dbBind[$hook] = $this->escape($value);
 
    }
 
    /**
     * Runs the SQL string in $dbSql object variable
     */
    public function run()
    {
        $sql = $this->dbSql;
        if(is_array($this->dbBind))
            foreach($this->dbBind as $hook => $value)
                $sql = str_replace($hook, "'" . $value . "'", $sql);
        $this->dbQuery = mysql_query($sql) or die(mysql_error());
        $this->dbBind = array();
        return $this->numRows();
    }
 
    /**
     * Used to fetch all rows form the last query in an
     * array. You can define the return type. Default is
     * MYSQL_ASSOC
     */
    public function fetchAll($type = MYSQL_ASSOC)
    {
        $tmpArr = array();
        while($row = mysql_fetch_array($this->dbQuery, $type))
        {
            $tmpArr[] = $row;
        }
        return $tmpArr;
    }
 
    /**
     * Returns one row array in associative format
     */
    public function fetchAssoc()
    {
        return mysql_fetch_assoc($this->dbQuery);
    }
 
    /**
     * Returns the number of rows affected by the last query
     */
    public function numRows()
    {
        return mysql_num_rows($this->dbQuery);
    }
 
    /**
     * Returns the id generated from the last query
     */
    public function insertId()
    {
        return mysql_insert_id($this->dbConn);
    }
 
}

Supporting PHP 4

If you want to be compatible with PHP 4, simply remove the scope definitions (public, private) from the front of class methods and variables.

Final Thoughts

Writing your own classes for repetitive PHP tasks not only boosts your productivity, but can be very rewarding. A common saying amongst programmers is “don’t re-invent the wheel”. I beg to differ. If we’re not constantly creating, updating and exploring, we’ll never move forward. Inventing new wheels is what makes you a kick-ass programmer.

This is a very basic class, and could easily be tweaked to suit your needs. Some ideas for expansion would be to add more detailed helper methods, or handle errors via the use of Exceptions instead of simple die functions.

If you’re new to class and objects, check out my post “A Beginners Guide To PHP Classes & Objects - Part 1“.