Refactoring the MySQL Abstraction Class with the Active Record Pattern

In this group of articles you’ll be provided with an approachable guide to building a database accessing class that will use the programming model imposed by the active record pattern for performing raw CRUD operations on a group of selected MySQL tables, and for fetching database records by using the WHERE, LIKE and LIMIT clauses as well. This is the sixth part of a seven-part series.

Introduction

When it comes to implementing the active record design pattern with PHP, in most cases data mapper objects are used to interact with a certain number of databases. Generally, these objects act like a single, unified interface that can be used to perform CRUD tasks (Create, Retrieve, Update, Delete) on mapped tables.

This schema obviously permits you to achieve a high level of SQL abstraction, since executing the mentioned tasks against a selected database table doesn’t require directly writing any SQL statements. All the business logic is handled behind the scenes by the objects in question.

However, it’s perfectly feasible to apply the active record pattern by using just a database accessing class, and still get an acceptable grade of SQL abstraction, particularly when it comes to inserting, updating and deleting records of a specific database table.

Now, returning for a moment to the topics discussed in the last installment, I left off explaining how to build a simple MySQL abstraction class, which had the ability to fetch a predefined number of records from a sample “users” MySQL table by using a LIMIT clause. In this particular case, a concrete method within the class was responsible for performing this task, without the need to code explicitly any SQL statements from outside the class.

As I stated in the end of the tutorial, however, some methods of this sample class implement redundant business logic, and as a consequence it’s necessary to refactor them to fix this issue in a quick and simple manner. Thus, in the next few lines I’ll be explaining how to accomplish this process progressively, and as always, accompanied by the corresponding code samples.

Now, it’s time to start modifying the signature of this MySQL abstraction class to make it more efficient and compact. Let’s go!

{mospagebreak title=Refactoring the MySQL sample class: modifying methods that perform CRUD operations}

To start refactoring the sample “MySQL” class built in the previous tutorial, the first step I’m going to take will consist of modifying its most basic methods; that is, the ones that perform CRUD operations. On the other hand, the methods tasked with connecting to the server and selecting a database will remain the same.

But before I proceed to introduce these changes to the class, it would be convenient to recall how it looked originally. Here it is:


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;

}

// fetch rows using ‘WHERE’ clause

public function fetchWhere($where,$table=’default_table’){

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

$rows=array();

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

$rows[]=$row;

}

return $rows;

}

// fetch rows using ‘LIKE’ clause

public function fetchLike($field,$like,$table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table.’ WHERE ‘.$field.’ LIKE ‘.$like);

$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);

}

// update row

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

$args=array();

foreach($params as $field=>$value){

$args[]=$field.’=”.$value.”’;

}

$sql=’UPDATE ‘.$table.’ SET ‘.implode(‘,’,$args).’ WHERE ‘.$where;

$this->query($sql);

}

// delete one or multiple rows

public function delete($where=”,$table=’default_table’){

$sql=!$where?’DELETE FROM ‘.$table:’DELETE FROM ‘.$table.’ WHERE ‘.
$where;

$this->query($sql);

}

// fetch rows using ‘LIMIT’ clause

public function fetchLimit($offset=1,$numrows=1,
$table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table.’ LIMIT ‘.$offset.’,’.$numrows);

$rows=array();

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

$rows[]=$row;

}

return $rows;

}

}


Having listed the complete signature of the “MySQL” class as it was built progressively in prior tutorials of the series, it’s time to begin refactoring some core methods to avoid business logic duplication. Thus, here’s the brand new definition of this sample class, this time including a few important modifications. Take a look at it, please:


class MySQL{

private $result;

private $select=’SELECT * FROM ‘;

private $where=’ WHERE ‘;

private $limit=’ LIMIT ‘;

private $like=’ LIKE ‘;

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($this->select.$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);

}

// update row

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

$args=array();

foreach($params as $field=>$value){

$args[]=$field.’=”.$value.”’;

}

$sql=’UPDATE ‘.$table.’ SET ‘.implode(‘,’,$args).$this->where.
$where;

$this->query($sql);

}

// delete one or multiple rows

public function delete($where=”,$table=’default_table’){

$sql=!$where?’DELETE FROM ‘.$table:’DELETE FROM ‘.$table.$this->where.$where;

$this->query($sql);

}

}


As shown before, the “MySQL” class now implements its CRUD methods much more intelligently. Each query is dynamically constructed by using the corresponding SQL statements, which have been stored previously as class properties. In doing so, it’s possible to easily define many other methods without having to use MySQL-related functions over and over again.

So far, so good. At this stage, I showed you how to specifically refactor the methods of the above “MySQL” class that are charged with performing CRUD tasks on a specified database table. Yet, as you might have guessed, it’s also necessary to modify the implementation of the remaining methods to complete the refactoring process.

Therefore, in the following section I’ll be showing you how to modify the rest of the class. But naturally, to see how those changes will be accomplished, you’ll have to click on the link below and keep reading.

{mospagebreak title=Completing the refactoring process of the MySQL abstraction class}

True to form, refactoring the remaining methods of the previous “MySQL” abstraction class is only a question of using its new properties to dynamically create the corresponding queries. In this case, I’m going to show you how to modify the “fecthWhere(),” “fetchLike()” and “fetchLimit()” methods that you saw before, so they can be used to fetch database rows from a selected database table using the respective “WHERE,” “LIKE” and “LIMIT” clauses.

That being explained, here’s the complete signature of this sample class, including all the modifications that I mentioned above:


class MySQL{

private $result;

private $select=’SELECT * FROM ‘;

private $where=’ WHERE ‘;

private $limit=’ LIMIT ‘;

private $like=’ LIKE ‘;

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($this->select.$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);

}

// update row

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

$args=array();

foreach($params as $field=>$value){

$args[]=$field.’=”.$value.”’;

}

$sql=’UPDATE ‘.$table.’ SET ‘.implode(‘,’,$args).$this->where.$where;

$this->query($sql);

}

// delete one or multiple rows

public function delete($where=”,$table=’default_table’){

$sql=!$where?’DELETE FROM ‘.$table:’DELETE FROM ‘.$table.$this->where.$where;

$this->query($sql);

}

// fetch rows using ‘WHERE’ clause

public function fetchWhere($where,$table=’default_table’){

$this->query($this->select.$table.$this->where.$where);

$rows=array();

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

$rows[]=$row;

}

return $rows;

}

// fetch rows using ‘LIKE’ clause

public function fetchLike($field,$like,$table=’default_table’){

$this->query($this->select.$table.$this->where.$field.$this->like.$like);

$rows=array();

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

$rows[]=$row;

}

return $rows;

}

// fetch rows using ‘LIMIT’ clause

public function fetchLimit($offset=1,$numrows=1,
$table=’default_table’){

$this->query($this->select.$table.$this->limit.$offset.’,’.$numrows);

$rows=array();

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

$rows[]=$row;

}

return $rows;

}

}


As I said before, the “fetchWhere(),” “fetchLike()” and “fetchLimit()” methods have been slightly modified, and now they use internally the private properties of the class to dynamically arm the corresponding SQL queries, in accordance with the type of clause that must be executed.

Provided that you now understand how this refactoring process has been accomplished, it’s time to set up a final practical example, where this improved version of the “MySQL” class will be used for inserting, updating and deleting rows of a sample MySQL table.

This concluding example will be created in the section to come. So go ahead and read the new few lines.

{mospagebreak title=Manipulating database records using the improved version of the MySQL class}

Without a doubt, the best way to test the improved version of the “MySQL” class is by means of a hands-on example that permits us to see how it actually functions. Therefore, below I included a short code sample that shows how to use the class for performing a few CRUD tasks and conditional queries against a sample “users” MySQL table. Here you have it:

try{

// connect to MySQL and select a database

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

// insert new row

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

// update row

$db->update(array(‘firstname’=>’Kathleen’,'lastname’=>’Johanson’,'email’=>
‘kate@domain.com’),’id=1′,’users’);

// delete row

$db->delete(‘id=1′,’users’);

// display all users

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

foreach($result as $row){

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

}

// display users where ID > 5

$result=$db->fetchWhere(‘id>5′,’users’);

foreach($result as $row){

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

}

// display users where first name contains the ‘a’ character

$result=$db->fetchLike(‘firstname’,"’%a%’",’users’);

foreach($result as $row){

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

}

// display users with the LIMIT clause

$result=$db->fetchLimit(2,4,’users’);

foreach($result as $row){

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

}

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Hopefully, the previous code sample should give you a clear idea of how simple it is to use the “MySQL” class for executing some typical queries against a specific MySQL table. Of course, one of the most important things to note here is the class’s ability to perform all of these operations via a friendly interface, and best of all, without having to write additional SQL statements.

Lastly, as usual with many of my articles on PHP web development, feel free to tweak all of the code samples shown in this tutorial, so you can acquire a better background in accessing MySQL databases with the active record pattern.

Final thoughts

In this sixth installment of the series, I explained how to refactor some methods that belong to the previous “MySQL” abstraction class to implement them a bit more efficiently and avoid needing to use redundant MySQL-related functions.

This educational journey isn’t finished yet, however, since there’s a final chapter ahead of us. It will be entirely focused on explaining how to adapt the signature of the pertinent MySQL class for working with the “mysqli” PHP extension.

Want to learn how this migration process will be achieved? Then don’t miss the last tutorial!

Google+ Comments

Google+ Comments