Running conditional SELECTS using the sample MySQL class - PHP

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.

TABLE OF CONTENTS:

By: Alejandro Gervasio

Poor Best

Rating:

/ 3

March 17, 2009

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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!