Using the Active Record Pattern with PHP and MySQL

Using the active record pattern to develop a database-driven application offers many advantages. It can save PHP developers a wealth of time in coding and code maintenance, because you can take advantage of SQL abstraction. This first part of a series will walk you through the basics of using the active record pattern.

Introduction

According to Wikipedia, “the active record pattern is a design pattern frequently found in software that stores its data in relational databases. Active record is an approach to accessing data in a database.” And admittedly, this definition accurately outlines the functionality of this pattern, even though it sounds hard to grasp at first. 

In many cases (though not all), implementing the active record pattern for a given web application requires the creation of a class instance that will be tied to each row of a database table. Based upon this schema, this instance will be used basically to select, update, insert and delete database records (also known as CRUD operations), without having to explicitly write any SQL statements.

Obviously, one of the major advantages in using the active record pattern when developing a database-driven application is the one described above: SQL abstraction. Instead of having to code raw, hard-to-maintain selects, inserts, updates, and deletion operations at different points of the application to access data stored in a database, the methods of a data mapper object are used to encapsulate all the SQL code necessary to perform all these tasks “behind the scenes.”

Utilizing data mapping objects isn’t the only way to apply the active record pattern, since it’s also feasible to achieve similar results by means of a database abstraction class that implements a bunch of methods that permit interaction with database tables without having to code SQL clauses from scratch.

Even though this approach isn’t as popular as using data mappers, it has been adopted progressively by many programmers nowadays. In the case of PHP, popular frameworks like CakePHP and Code Igniter use this approach for implementing active record classes in a pretty successful way.

Thus, in this series of articles, I’ll be explaining how to develop an active record class in PHP, which will allow you to perform selects, updates, inserts, and deletion operations against a selected MySQL database table in a simple fashion. As is typical of my approach, I’ll use numerous code samples to make my points clear. 

I’ll start with a class written in PHP 4 that will implement a few simple methods, and then, when you understand how it works, I’ll add more complexity and functionality.

Are you ready to learn how to apply the active record pattern with PHP? Then don’t waste more time in preliminaries; start reading now!

{mospagebreak title=Building a simple data mapper in PHP}

A good way to start explaining how to use the active record pattern in PHP consists of creating a quick and dirty data mapper class. This class will be tied to a specified MySQL database table to select, insert, update and delete its rows, without having to code any SQL queries.

That being said, suppose for a moment that there’s a sample “users” table that will be accessed via a data mapper class. According to this scenario, the primitive signature of this class might look similar to this:


class User{

private $firstName;

private $lastName;

private $email;

private $table=’users’;

public function __construct(){}

// set first name

public function setFirstName($firstName){

$this->firstName=$firstName;

}

// set last name

public function setLastName($lastName){

$this->lastName=$lastName;

}

// set email

public function setEmail($email){

$this->email=$email;

}

// fetch row

public function fetch($id){

if(!$row=mysql_query("SELECT * FROM $this->table WHERE id=’$id’")){

throw new Exception(‘Error fetching row’);

}

return $row;

}

// insert row

public function insert(){

if(!mysql_query("INSERT INTO $this->table (firstname,lastname,email) VALUES ($this->firstName,$this->lastName,$this->email)")){

throw new Exception(‘Error inserting row’);

}

}

// update row

public function update($id){

if(!mysql_query("UPDATE $this->table SET firstname=’$this->firstName,lastname=$this->lastName,email=$this->email WHERE id=’$id’")){

throw new Exception(‘Error updating row’);

}

}

// delete row

public function delete($id){

if(!mysql_query("DELETE FROM $this->table WHERE id=’$id’")){

throw new Exception(‘Error deleting row’);

}

}

}


As you can see, the above “User” class implements a set of straightforward methods for selecting inserting, updating and deleting rows from a fictional “users” MySQL database table. Notice that each of these operations works with only one row at time, in this way behaving like a simple interface that permits you to access data in the table.

Now that you hopefully understood how the previous data mapper class does its thing, here’s a basic example that demonstrates its usage in a concrete case. Take a look at the code sample below:


try{

$user=new User();

// set first name

$user->setFirstName(‘Alejandro’);

// set last name

$user->setlastName(‘Gervasio’);

// set email

$user->setEmail(‘alejandro@domain.com’);

// insert row

$user->insert();


// set first name

$user->setFirstName(‘Alejandro’);

// set last name

$user->lastName(‘Gervasio’);

// set email

$user->setEmail(‘alex@domain.com’);

// update row

$user->update(1);

// delete row

$user->delete(1);

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


As shown above, the previous sample class uses the active record pattern to insert, update and delete a single row from the corresponding “users” MySQL table. It’s easy to see here how the pattern permits us to perform these tasks in an intuitive manner, even when no SQL statements have been explicitly coded.

So far, everything looks good. At this point, you’ve learned how to build a simple class with PHP 5 which utilizes the active record pattern for interacting with a selected MySQL table. However, as I expressed at the beginning, it’s also possible to achieve a decent level of SQL abstraction without using a data mapper class like the one shown before.

In fact, it’s perfectly feasible to create a simple database abstraction class that performs selects, updates, deletions, and so forth, without directly writing any SQL clauses. And this is exactly what I’m going to do in the next section, so you can understand more clearly how the active record pattern can be applied by using a slightly different approach.

In order to learn how this brand new PHP class will be defined, please click on the link that appears below and read the next few lines.

{mospagebreak title=Building a basic MySQL abstraction class}

True to form, the active record pattern can be also implemented via a simple database abstraction class. In this particular situation, I’m going to demonstrate how this concept can be applied specifically to a PHP 5 class that works with MySQL.

As you’ll see in a moment, this class not only will be capable of performing a few common tasks, such as connecting to the server and selecting a specific database, but running some CRUD operations as well.

But it’s time to get rid of the boring theory and show some functional code. So here’s the signature of a rudimentary active record class:


// define ‘MySQL’ class

class MySQL{

private $result;

public function __construct($host=’localhost’,$user=’user’,$password=’password’,$database=’database’){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception(‘Error connecting to the server’);

}

if(!mysql_select_db($database,$conId)){

throw new Exception(‘Error selecting database’);

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception(‘Error performing query ‘.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

}


As shown before, the signature of the above “MySQL” class doesn’t differ too much from any MySQL abstraction class that you might have coded before. In this case, it implements a set of methods that come in useful for connecting to the database server, running SQL queries and fetching one row at a time from a returned result set. So far, nothing unexpected, right?

However, I’d like you to pay close attention to the method called “fetchAll().” As you can see, it executes a “SELECT * FROM” SQL statement behind the scenes to fetch all the rows from a specified MySQL table. Even so, the method only takes up the name of the table to be queried. No additional SQL clauses are coded explicitly, in this way considerably abstracting this process.

By following an approach like the one I used to code the previous “fetchAll()” method, it’s feasible to implement the active record pattern in a pretty simple manner, while obtaining an acceptable level of SQL abstraction.

To demonstrate the functionality of the previous “MySQL” class, suppose that there’s a MySQL table called “users,” which has been previously populated with the following data:



Now that there’s a database table to work with, take a look at the following example. It uses the database abstraction class to fetch all of the rows from the above MySQL table:

try{

// connect to MySQL and select a database

$db=new MySQL(‘host’,'user’,'password’,'mydatabase’);

$result=$db->fetchAll(‘users’);

foreach($result as $row){

echo $row['firstname'].’ ‘.$row['lastname'].’ ‘.$row['email'].’<br />’;

}


/* displays the following


Alejandro Gervasio alejandro@domain.com
John Doe john@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com
Jodie Foster jodie@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com
Jennifer Aniston jennifer@domain.com


*/

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


In this case, the active record pattern implemented via the “fetchAll()” method permits us to retrieve all the rows contained in the previous “users” MySQL table without having to directly write a “SELECT” statement, since the method handles this process automatically.

Now that you hopefully understood how the previous “MySQL” class is capable of fetching all the rows from a selected MySQL table, it’s time to move forward and extend its functionality. Therefore, in the section to come I’ll be providing this class with the capability for inserting new records into a selected table via the active record pattern.

To see how this will be achieved, please click on the link below and read the next few lines.

{mospagebreak title=Inserting database table rows with the active record pattern}

Actually, providing the previous “MySQL” abstraction class with the ability to insert a new database row via the active record pattern is only a matter of coding a method that constructs internally an “INSERT” SQL statement and nothing else.

To illustrate this concept more clearly, below I included the signature of this class, this time including a method that inserts new records into a specified MySQL table. Take a look at it, please:


class MySQL{

private $result;

public function __construct($host=’localhost’,$user=’user’,$password=’password’,$database=’database’){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception(‘Error connecting to the server’);

}

if(!mysql_select_db($database,$conId)){

throw new Exception(‘Error selecting database’);

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception(‘Error performing query ‘.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// insert row

public function insert($params=array(),$table=’default_table’){

$sql=’INSERT INTO ‘.$table.’ (‘.implode(‘,’,array_keys($params)).’) VALUES (”.implode("’,'",array_values($params)).”)’;

$this->query($sql);

}

}


Definitely, things are getting more interesting now. As you can see, the above “MySQL” class incorporates a brand new method called “insert(),” which can be used for adding new rows to a specified MySQL table.

It’s clear to see how the method internally constructs an “INSERT” SQL statement, by using the array of input parameters that is passed into it, along with the specified MySQL table. Nonetheless, to dissipate any possible doubts that you might have regarding how the “insert()” method works, please take a close look at the following example:


try{

// connect to MySQL and select a database

$db=new MySQL(‘host’,'user’,'password’,'mydatabase’);

// insert new row into selected MySQL table

$db->insert(array(‘firstname’=>’Kate’,'lastname’=>’Johanson’,'email’=>’kate@domain.com’),’users’);

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


As shown above, that’s all the code required to add a new record to the sample “users” MySQL table by means of the active record pattern. In this case, the above “insert()” method hides from the outside all of the complexity demanded by the insertion operation, without dealing directly with SQL code.

With this last example, I’m finishing this introductory tutorial on using the active record pattern within a MySQL abstraction class. As I explained in the beginning, the approach that I used here to apply the pattern differs from using data mapper objects, but it can be easier to implement.

Final thoughts

In this first installment of the series, I demonstrated how to apply the active record pattern in PHP 5, by means of a basic MySQL abstraction class. In this case, the class not only abstracts common tasks, such as connecting to the server and selecting a specific database, but selecting and inserting rows, since all of these operations were handled behind the class API.

In the upcoming article, I’m going to extend the functionality of this sample class to provide it with the capacity for updating and deleting database records without having to write a single portion of SQL code. You won’t want to miss the next tutorial!

Google+ Comments

Google+ Comments