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!