Home arrow PHP arrow Page 4 - Utilizing the LIMIT Clause with the Active Record Pattern

Example: fetching database rows with the LIMIT clause - PHP

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.

  1. Utilizing the LIMIT Clause with the Active Record Pattern
  2. Review: fetching database records using the LIKE clause
  3. Enhancing the functionality of the MySQL class with the LIMIT clause
  4. Example: fetching database rows with the LIMIT clause
By: Alejandro Gervasio
Rating: starstarstarstarstar / 6
March 31, 2009

print this article



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:


// connect to MySQL and select a database

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

// display users with the LIMIT clause


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



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!

>>> More PHP Articles          >>> More By Alejandro Gervasio

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: