 PHP Page 4 - The mysqli Extension and the Active Record Pattern |
The sample MySQL class in action - PHPThe 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.
print this article SEARCH DEV SHED |
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 // 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 */ // 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 */ // 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 */ // 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 */ } 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!
blog comments powered by Disqus |
/ 4 
Implementing the commit() and rollback() Methods with mysqli and PHP 5
