Home arrow PHP arrow Page 4 - The mysqli Extension and the Active Record Pattern

The sample MySQL class in action - PHP

The active record pattern allows you to access records in a database by means of a unified interface, generally composed of a few data mapper objects. However, as with other popular design patterns, there are several methodologies for implementing it within an object-oriented application, all of which can be used to obtain similar results. In this group of articles, I demonstrate how to progressively build a MySQL abstraction class that uses the active record approach for performing CRUD operations (Create, Retrieve, Update, Delete) on the records of a selected database table.

TABLE OF CONTENTS:
  1. The mysqli Extension and the Active Record Pattern
  2. Getting started using the mysqli PHP extension
  3. Finish updating the MySQL class
  4. The sample MySQL class in action
By: Alejandro Gervasio
Rating: starstarstarstarstar / 4
April 14, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

If you’re anything like me, then it’s quite possible that you may want to see how the previous “MySQL” class can be put to work after rewriting all of its methods. Therefore, in the next few lines I included a number of code samples to demonstrate how to use the class for performing some basic database operations on the same “users” MySQL table utilized in previous tutorials.

In case you don’t recall how this sample table was populated, here it is:



Now that there’s a MySQL table to work with, here are the code samples that utilize it:


try{

// connect to MySQL and select a database

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

// insert new row

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

// update row

$db->update(array
('firstname'=>'Kathleen','lastname'=>'Johanson','email'=>'kate@domain.com'),'id=1','users');

// delete row

$db->delete('id=1','users');

// display all users

$result=$db->fetchAll('users');

foreach($result as $row){

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

}


/* displays the following


Alejandro Gervasio alejandro@domain.com
John Doe john@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
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

*/

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

*/

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


*/

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

*/

}


Undoubtedly, the set of examples listed above clearly illustrate how the updated “MySQL” class can be used to perform different queries against a simple MySQL table, through a simple interface.

Even though the class implements a slightly different version of the active record pattern, the level of SQL abstraction achieved is more than acceptable, considering that it’s been coded for only illustrative purposes.

Feel free to modify all the code samples included into this article, so you can improve your existing skills in using the active record approach with PHP.

Final thoughts

Now that we’ve come to the end of this series, there’s a couple of options that you can try: either you can code your own active record class based on the examples developed in these tutorials, or you can use one included with a third-party framework. In either case, you’ll be armed with a solid background in how to use this pattern to access your databases.

See you in the next PHP web development tutorial!



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- 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: