Utilizing the LIMIT Clause with the Active Record Pattern

Welcome to the fifth installment of a series focused on using the active record pattern with PHP. Made up of seven episodes, this series walks you through the progressive development of a MySQL abstraction class that utilizes the active record approach to perform CRUD (Create, Remove, Update, Delete) database operations and SELECT queries, without having to explicitly code any SQL statements.

Introduction

Now that you’ve been introduced to the main subject of this group of articles, it’s time to recall the topics that were covered in the last one. In that specific tutorial I explained how to build a basic MySQL abstraction class, which was capable of performing the CRUD tasks mentioned before, in addition to fetching database records using the LIKE clause.

Of course, the structure of this class is rather primitive and definitely wasn’t suited for use in production environments. Despite this disadvantage, it came in useful for demonstrating how it can be utilized for accessing MySQL databases by means of the active record approach.

However, it’s fair to mention that the functionality of the class can be enhanced. In its current version, it’ll be able to accomplish only a few basic database operations tasks. Therefore, this fifth article of the series will be focused on enabling the class to fetch database rows by using the LIMIT clause.

By following the same approach demonstrated in preceding articles, this brand new capacity will be implemented through the active record pattern, in this manner extending its utilization within a MySQL abstraction class even more.

Now, it’s time to get our hands dirty with a few functional PHP code samples. Let’s jump in!

{mospagebreak title=Review: fetching database records using the LIKE clause}

As I said in the beginning, the sample “MySQL” class that I used in the previous article was capable of fetching rows from a specified database table by using a straightforward method, called simply “fetchLike().”

It’d be helpful to recall briefly how the signature of this class looked originally, however, before I proceed to extend its existing functionality via the active record pattern. Therefore, below I included the definition of the class, along with a simple example that demonstrates how it can be used to retrieve all the records from a “users” MySQL table whose “firstname” field contain the “a” character. 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);

}

}


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

}


As you’ll recall, the above example illustrates how to utilize the pertinent “MySQL” class to fetch a few trivial records from a “users” MySQL table. In this case, the method used to retrieve this data is called “fetchLike()” and, as its name suggests, performs SELECT queries that include a LIKE clause.

Naturally, the most important detail to spot here is this method’s ability to execute the aforementioned SQL query through a friendly interface that eliminates the need for coding SQL statements from scratch.

Now that you’ve recalled how the prior “MySQL” class does its business, it’s time to continue extending its existing functionality. As I stated in the introduction, I’m going to define a new method within this class, which will be charged with retrieving rows from a targeted MySQL table by using a LIMIT clause. This could be pretty useful when it’s necessary to build a record pagination mechanism.

This method will be built in the next section, so click on the link shown below and keep reading.

{mospagebreak title=Enhancing the functionality of the MySQL class with the LIMIT clause}

Undoubtedly, defining a method within the previous “MySQL” class that permits us to fetch a limited number of rows from a specified MySQL table would be a neat add-on. Moreover, if this method allows us to perform this task without having to write SQL code directly, this is even better.

Therefore, the next thing that I’m going to do will be exactly that, that is, include a method that runs SELECT queries by using a LIMIT clause. The signature of the “MySQL” class, after coding this brand new method, will be the following:


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;

}

}


Now the above class is becoming a pretty lengthy, right? Don’t worry; I listed separately for you the signature of this new method, called “fetchLimit().” Here it’s what it looks like:


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

}


As you can see, all that the “fetchLimit()” method does is encapsulate the logic required to fetch a specified number of database records from a selected MySQL table by means of the classic LIMIT clause.

Since this method is indeed very simple to grasp, I’m not going to bore you with additional explanations about how it functions. Instead, I’ll develop a practical example, where you can see it in action. The last section of this tutorial will be dedicated exclusively to setting up this concrete example.

Please click on the link below and read the next section. We’re almost finished!

{mospagebreak title=Example: fetching database rows with the LIMIT clause}

To illustrate how the “fetchLimit()” method created earlier can be used to retrieve a specified number of records from a MySQL table, I first need to have one to work with. So, I’m going to use the same “users” table that you saw in some previous tutorials, which contained data about a few people. Here it is:



Now that the above MySQL table is available for use, it’s time to create a short code sample that shows how to utilize the “fetchLimit()” method included in the “MySQL” class. In this specific case, the example below retrieves four users, starting from the third row. Take a look at it, please:

try{

// connect to MySQL and select a database

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

// display users with the LIMIT clause

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

foreach($result as $row){

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

}


/* displays the following


Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com


*/


}

catch(Exception $e){

echo $e->getMessage();

exit();

}


See how simple it is to use the prior “fetchLimit()” method to fetch a predefined number of records from the sample “users” MySQL table? I bet you do! As show above, the method allows you to perform LIMIT clauses using a modified version of the active record approach, and best of all, through a friendly interface.

Finally, feel free to introduce your own enhancements into the previous “MySQL” class (possibly checking SQL queries more thoroughly, adding more methods, etc), in this manner extending your skills in utilizing the active record pattern with PHP.

Final thoughts

We’ve come to the end of this fifth installment of the series. Overall the experience has been hopefully instructive, because you learned how to build a basic MySQL abstraction class that performs a few common database-related operations using the active record pattern.

In this case, no mapper objects were used, as a typical implementation of this pattern would do, but in the end, the class’s API permits you to access MySQL tables through a highly abstract and friendly interface, which could be quite useful when working with multiple database servers.

However, there’s a point with reference to the way this class works that deserves a close analysis. Obviously, its methods often duplicate business logic. For instance, why code two different methods that perform SELECT statements (even if they append distinct SQL clauses to the end), when they can be merged into a single one that builds queries dynamically?

This specific issue, and a few others, will be addressed in the next article, so you don’t have any excuses to miss it!

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