Manipulating database records using the improved version of the MySQL class - PHP

Without a doubt, the best way to test the improved version of the “MySQL” class is by means of a hands-on example that permits us to see how it actually functions. Therefore, below I included a short code sample that shows how to use the class for performing a few CRUD tasks and conditional queries against a sample “users” MySQL table. Here you have 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 />';

}

// display users where ID > 5

$result=$db->fetchWhere('id>5','users');

foreach($result as $row){

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

}

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

}

// display users with the LIMIT clause

$result=$db->fetchLimit(2,4,'users');

foreach($result as $row){

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

}

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

Hopefully, the previous code sample should give you a clear idea of how simple it is to use the “MySQL” class for executing some typical queries against a specific MySQL table. Of course, one of the most important things to note here is the class’s ability to perform all of these operations via a friendly interface, and best of all, without having to write additional SQL statements.

Lastly, as usual with many of my articles on PHP web development, feel free to tweak all of the code samples shown in this tutorial, so you can acquire a better background in accessing MySQL databases with the active record pattern.

Final thoughts

In this sixth installment of the series, I explained how to refactor some methods that belong to the previous “MySQL” abstraction class to implement them a bit more efficiently and avoid needing to use redundant MySQL-related functions.

This educational journey isn't finished yet, however, since there’s a final chapter ahead of us. It will be entirely focused on explaining how to adapt the signature of the pertinent MySQL class for working with the “mysqli” PHP extension.

Want to learn how this migration process will be achieved? Then don’t miss the last tutorial!