Running Conditional Select Statements with the Active Record Pattern

If you’re a PHP programmer who wants to learn how to implement the active record pattern within a MySQL abstraction class to make your database-driven applications more robust and maintainable, then look no further. Welcome to the third part of a series focusing on the active record pattern. Made up of seven articles, this series explains how to take advantage of the functionality provided by this popular design pattern to build an extensible MySQL-accessing class. This class will allow you to perform several queries without explicitly writing any SQL statements.

Introduction

Now that you’re aware of the main goal of this group of tutorials, it’s time to review the topics we covered in the last article. In that part, I walked you through the development of a basic MySQL abstraction class, which was capable of fetching, inserting, updating and deleting database records (also known as CRUD operations) by means of the active record approach.

Basically, this implied that the MySQL-accessing class was provided with the capability for running all of these queries without the need to manually code the corresponding SQL clauses, a feature that helped to abstract SQL code.

However, as you may have guessed, there’s plenty of room for enhancing the functionality of this simple class. With that idea in mind, in this third chapter of the series I’m going to show you how to add a new method to the class. It will be charged with executing conditional SELECT statements via a modified version of the active record pattern.

Now, with the preliminaries out of our way, it’s time to dig deeper into this interesting topic. Let’s get going!

{mospagebreak title=Review: performing CRUD operations through the active record approach}

You may wonder how we’re going to provide the MySQL abstraction class built in the previous article with the ability for performing conditional SELECT statements using the active record pattern. Before I proceed to discuss that particular topic, however, it would be useful to list the signature of this class in its current state. As I mentioned, it was capable of performing only CRUD tasks (Create, Remove, Update, Delete).

That being said, here’s how this MySQL-accessing class looked originally:


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

}

// 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 illustrated above, the previous “MySQL” class implements a few straightforward methods for fetching, inserting, updating and deleting rows in a specified MySQL table. As I said before, all of these operations are performed without the need to directly code any SQL commands, as you can see clearly in the code sample above.

Now that you’ve recalled how this basic “MySQL” class was defined, here’s a short example that shows how it can be used for consecutively adding and deleting one single row from a fictional “users” MySQL table:


try{

// connect to MySQL and select a database

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

// insert new row into sample MySQL table

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

// delete row from sample MySQL table

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

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Undoubtedly, the above hands-on example demonstrates in a nutshell how to use the “MySQL” class for inserting a new row into a sample “users” MySQL table, and how to delete it later on, by means of its respective “insert()” and “delete()” methods. This process is actually very simple to follow.

Well, at this point you hopefully recalled how to use the previous class to perform CRUD operations via the active record approach. However, as I mentioned in the introduction, it’s perfectly possible to incorporate many other methods into the class to extend its existing functionality.

Bearing this idea in mind, in the section to come I’ll be adding a brand new method to the sample MySQL class. In doing so, it’ll be able to perform simple conditional SELECT queries, without coding any explicit SQL statements.

To learn the full details of how this new method will be defined, please click on the link that appears below and read the next few lines.

{mospagebreak title=Performing conditional SELECTS through the active record pattern}

In reality, providing the previous “MySQL” class with the ability to perform conditional SELECT statements using the active record pattern only involves coding a brand new method, which will carry out this task behind the scenes. Basically, the method will look very similar to the “fetch()” that you learned before, except that in this case, it’ll append a WHERE clause to the end of the SELECT command.

However, the best way for you to understand how this method will work is by showing you its corresponding signature, which looks like this:


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

}


Didn’t I tell you that this new method would be very simple to grasp? And if you study its implementation, you’ll have to agree with me on this. But let me explain how it works, in case you have some trouble understanding its logic.

As you can see, it looks nearly identical to the “fetch()” method, except that this one aggregates a WHERE clause to the end of the SQL query, in this way constructing the conditional SELECT.

Of course, if you examine in detail how this method functions, then you’ll realize that it can be improved, but its current functionality is more than enough to demonstrate how simple it is to define a method like this.

At this stage, I’m sure that you now understand how the above “fetchWhere()” method does its thing, so it’s time to list the enhanced version of the “MySQL” class, this time including this method. 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;

}

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

}

}


At this point, the “MySQL” class is indeed quite useful, since it’s capable not only of performing CRUD database operations, but also has the ability to execute conditional SELECTS. Even so, there’s one thing that remains undone; it’s necessary to develop an example that shows the actual functionality of the  “fecthWhere()” method.

The last section of this tutorial will be dedicated to setting up such an example, so click on the link that appears below and keep reading. We’re almost finished!

{mospagebreak title=Running conditional SELECTS using the sample MySQL class}

To demonstrate how the “fetchWhere()” method defined in the previous section can be used in a concrete situation, it is necessary to have at our disposal a sample MySQL table with which to work. Therefore, I’m going to use the one that was built in the first article, whose structure looked like this:



Now that there’s a “users” MySQL table available for testing purposes, here’s the practical example that demonstrates the functionality of the “fetchWhere()” method:

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

}


That was pretty easy to grasp, wasn’t it? By means of the “fetchWhere()” method it is very simple to perform conditional SELECTS, and best of all, no SQL statements need to be written from scratch.

In addition, it’s fair to mention that this method doesn’t strictly follow the model imposed by the active record pattern. Nevertheless, it does implement a useful variation of it, even though no data mappers are used in this particular case.

Final thoughts

Over the course of this third part of this series I illustrated, with an easy-to-grasp MySQL abstraction class, how to perform conditional SELECT queries against a specified database table. Hopefully, the code samples included in this tutorial will inspire you to build your own active record classes. Fun is already guaranteed!

In the forthcoming article, I’ll explain how to execute LIKE clauses by way of the aforementioned MySQL class. Therefore, now that you know what the next article will be about, you don’t have any excuses to miss it!

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort