The LIKE Clause and the Active Record Pattern

In most cases, the implementation of the active record pattern in PHP (and other programming languages too) is carried out through a few data mapper objects, which are used to perform CRUD operations on a group of targeted database tables. This seven-part article series describes the advantages of using the active record pattern in a variety of situations, and shows you how to do it.

Introduction

While an approach like the one described above permits to reach a high level of SQL abstraction when accessing databases, the truth is that it’s also possible to apply the active record approach by means of a single database class, which encapsulates within its API all the logic required for executing CRUD tasks.

Logically, when using this methodology, programmers won’t need to code  any SQL statements directly, since the class will dynamically construct the SQL queries required for performing records insertions, updates, deletions and so forth.

In this group of articles, you’ll be provided with an approachable guide to building a database abstraction class like the one mentioned above, which will let you easily perform the most common database-related operations without having to write a single portion of SQL code.

At this point, you possibly recall that in the preceding tutorial I explained how to build a simple MySQL abstraction class, which could run conditional SELECT statements by utilizing a slightly different version of the active record pattern. Essentially, this class was capable not only of dynamically arming SELECT queries, but working with WHERE clauses, which made running conditional SQL commands a no-brainer process.

However, in its current incarnation, this class’s functionality is limited, regardless of its decent level of SQL abstraction. Therefore, in this fourth part of the series I’m going to enhance it by making it capable of working with LIKE clauses in a straightforward way.

Now it’s time to get rid of the preliminaries and continue extending the functionality of the MySQL-accessing class developed in the previous article. Let’s get going!

{mospagebreak title=Review: conditional SELECT queries with the active record pattern}

First I’d like to reintroduce the practical example created in the last installment of the series. It demonstrated how to build a MySQL abstraction class capable of performing both basic CRUD database operations and conditional SELECTS.

Originally, the aforementioned example, including the signature of the “MySQL” class looked like this:


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;

}

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

}

}


try{

// connect to MySQL and select a database

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

// display users where ID > 5

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

foreach($result as $row){

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

}


/* displays the following


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

}


Now that you’ve examined the above hands-on example, you should be familiar with the logic implemented by the previous class to insert, update and delete rows in a specified MySQL table using the active record approach.

In addition to executing the task mentioned before, it’s worth noting that this sample class can run conditional SELECT queries by means of a highly-abstracted interface, in this case implemented via the “fetchWhere()” method.

At this point, and assuming that you’ve grasped how the “MySQL” class works, it’s time to continue extending its functionality. So, in accordance with the concepts deployed in the introduction, in the next few lines I’m going to show you how to adapt the class in question so it can work with SQL LIKE clauses.

This interesting topic will be discussed in the following section, so click on the link below and keep reading.

{mospagebreak title=Working with LIKE clauses}

By using an approach similar to the one for performing conditional SELECT statements, it’s also possible to dynamically construct SQL queries that include a typical LIKE clause. In this case, to demonstrate this concept more clearly, I’m going to add a new method to the previous “MySQL” class. This will let you see how it can be used to execute LIKE statements in a concrete situation.

That being explained, please pay attention to the enhanced signature of this sample class, which now looks like this:


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

}

}


As shown above, the “MySQL” class now implements a brand new method, called “fetchLike().” It is used for performing SELECT statements using the “LIKE” clause. The implementation of this method is included below:


// 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;

}


In this case, the method permits you to run queries without having to directly write any SQL commands, thus abstracting the process considerably. Naturally, there’s plenty of room to improve the logic of the “fetchLike()” method since it’s  pretty simplistic, but the most important thing here is that you grasp how it functions.

So far, so good. At this point, you’ve hopefully learned how to use the active record approach (or a modified version of it, to be fair) to work with LIKE clauses. However, if you’re like me, you want to see a practical example where the method can be tested.

Therefore, in the following section I’ll be setting up this example for you, so please jump forward and read the next segment. It’s only one click away.

{mospagebreak title=The fetchLike() method in action}

In the previous section, I explained how to aggregate a new method to the sample “MySQL” class to provide it with the capacity for performing SELECT statements that include a LIKE clause. However, the best way for you to understand how this enhanced version of the class functions is by means of a concrete example.

With that idea in mind, I’m going to use the sample “users” MySQL table created in a previous tutorial of this series, which looked like this:



Now that the above MySQL table is available for testing purposes, here’s a simple example that shows how to use the “fetchLike()” method defined earlier to fetch all the users whose first names contain the “a” character:


try{

// connect to MySQL and select a database

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

// 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 />’;

}


/* displays the following


Alejandro Gervasio alejandro@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com


*/


}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Undoubtedly, you’ll have to agree with me that the above example is very simple to grasp, since it uses the aforementioned “fetchLike()” method to perform a basic SELECT query that includes the LIKE clause. Obviously, regardless of its simplicity, the example is helpful for illustrating how to use the active record approach to execute different types of queries by means of a simplified, abstract interface.

As usual, feel free to introduce your own modifications to the code samples included in this tutorial. Doing this will surely give you a more solid grounding in using this popular design pattern in PHP 5.

Final thoughts

In this fourth chapter of the series, you hopefully learned how to use a basic MySQL abstraction class to perform SELECT statements in conjunction with simple LIKE clauses via the active record approach. As you saw before, this process only required coding a straightforward method and putting it to work. It was that simple, really.

In the forthcoming episode, I’m going to demonstrate how to utilize the same “MySQL” class coded earlier for running SELECT queries that contain a LIMIT clause.

The topic will be juicy, so if you wish to master it in a few steps, then don’t miss the next part!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan