Updating and Deleting Database Records with the Active Record Pattern

Regardless of its rather intimidating name, the active record pattern is actually quite simple to implement with PHP when developing database-driven applications. Indeed, this pattern permits you to achieve a high level of abstraction when performing typical operations, such as insertions, updates and deletions of records, since all of these tasks are handled transparently by data mappers objects or a data access class.

Introduction

Therefore, if you’re interested in learning how to implement this useful pattern within a MySQL abstraction class, then this series of articles might be what you really need. In it you’ll find numerous examples that will help you to master this topic by mean of a hands-on approach, and best of all, with minor hassles.

Of course, if you already read the previous article, then you’re familiar with utilizing the active record pattern in conjunction with a MySQL abstraction class. In that particular tutorial I discussed this topic extensively, and hopefully this experience has been instructive for you.

In simple terms, this MySQL-handling class was provided with the ability to fetch and insert database rows without the need to code explicitly any SQL statements. This can be fairly helpful in achieving a neat level of SQL abstraction.

However, this educational journey has just started. It’s possible to apply this pattern to update and delete rows from a selected MySQL table. Therefore, in the next few lines I will demonstrate how to perform updates and deletions through the sample MySQL class developed in the previous tutorial.

Now, it’s time to learn how to use the active record pattern for performing database updates and deletions in a few simple steps. Let’s jump in!

{mospagebreak title=Review: fetching and inserting database rows with the active record pattern}

Before I show you how to use the MySQL abstraction class created in the first article to update and delete database rows, let’s recall very quickly how this class originally looked.

Here’s the class’ complete signature, along with a couple of additional examples that show how to fetch all the rows from a sample “users” MySQL table and how to insert new records:


class MySQL{

private $result;

public function __construct($host=’localhost’,$user=’user’,$password=’password’,$database=’database’){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception(‘Error connecting to the server’);

}

if(!mysql_select_db($database,$conId)){

throw new Exception(‘Error selecting database’);

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception(‘Error performing query ‘.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// insert row

public function insert($params=array(),$table=’default_table’){

$sql=’INSERT INTO ‘.$table.’ (‘.implode(‘,’,array_keys($params)).’) VALUES (”.implode("’,'",array_values($params)).”)’;

$this->query($sql);

}

}


try{

// connect to MySQL and select a database

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

$result=$db->fetchAll(‘users’);

// fetch all rows from sample MySQL table

foreach($result as $row){

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

}

// insert new row into sample MySQL table

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

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Undoubtedly, understanding how the above “MySQL” class works isn’t rocket science, right? As you’ll probably recall, other than performing “conventional” queries against a selected database, the class includes two relevant methods, called “fetchAll()” and “insert()” respectively, which use the active record approach for fetching all the records from a specified MySQL table and for adding new ones as well.

In this particular case, the implementation of these handy methods permits us to encapsulate the logic required for constructing SELECT and INSERT statements, in this manner achieving a good level of SQL abstraction.

All in all, at this point I’m sure that you’ve grasped how the prior “MySQL” class functions. Thus, it’s time to see how to expand the functionality of the class. In its current state, it’s capable of fetching and inserting rows, so next I’ll define a new method to update a row within a MySQL table.

However, to see how this brand new method will be created, you’ll have to click on the link below and read the following section.

{mospagebreak title=Updating database rows with the active record pattern}

Any decent class that uses the active record pattern to interact with a database must be capable of updating its records, and the one that I’m building here isn’t an exception. Therefore, I’m going to define a brand new method within the previous “MySQL” class, called “update().” It will be responsible, obviously, for updating a single row in a specified MySQL table.

Now that I have explained how this method will work, please take a look at the enhanced signature of the “MySQL” class, which incorporates the “update()” method mentioned above. Here it is:


class MySQL{

private $result;

public function __construct($host=’localhost’,$user=’user’,$password=’password’,$database=’database’){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception(‘Error connecting to the server’);

}

if(!mysql_select_db($database,$conId)){

throw new Exception(‘Error selecting database’);

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception(‘Error performing query ‘.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// insert row

public function insert($params=array(),$table=’default_table’){

$sql=’INSERT INTO ‘.$table.’ (‘.implode(‘,’,array_keys($params)).’) VALUES (”.implode("’,'",array_values($params)).”)’;

$this->query($sql);

}

// update row

public function update($params=array(),$where,$table=’default_table’){

$args=array();

foreach($params as $field=>$value){

$args[]=$field.’=”.$value.”’;

}

$sql=’UPDATE ‘.$table.’ SET ‘.implode(‘,’,$args).’ WHERE ‘.$where;

$this->query($sql);

}

}


As shown above, the previous “MySQL” class now includes an “update()” method. It’s useful for updating an existing record within a specific MySQL table. Also, it’s worthwhile to mention here that the method in question accepts a “$where” input argument, which will be used internally for constructing the corresponding conditional UPDATE statement. Not too difficult to understand, right?

Well, assuming that you already grasped the logic that drives the prior “update()” method, please pay attention to the following code sample, which shows how to update a record of the sample “users” MySQL table created in the first tutorial:


try{

// connect to MySQL and select a database

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

// insert new row into sample MySQL table

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

// update row of sample MySQL table

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

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


See how simple it is to update an existing database record by using the active record approach? I bet you do! And naturally, one of the major advantages in using it is that no SQL statements have to be written to perform this specific operation.

Okay, at this stage, the previous “MySQL” class has been provided with the capability for fetching, inserting and updating database rows. So what’s next? Yes, you guessed right. To extend the functionality of the class even more, it’s necessary to define another method that allows us to delete records from a specified MySQL table.

Therefore, to learn how this brand new method will be implemented, click on the link that appears below and read the following section. 

{mospagebreak title=Deleting database rows with the active record approach}

If you quickly learned how to use the active record approach to update records of a specified MySQL table, then surely you’ll find it even easier to implement a method that deletes them. No, really. But don’t take my word for it; look at the improved version of the previous “MySQL” class, which now incorporates a brand new method called “delete().” Here it is:


class MySQL{

private $result;

public function __construct($host=’localhost’,$user=’user’,$password=’password’,$database=’database’){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception(‘Error connecting to the server’);

}

if(!mysql_select_db($database,$conId)){

throw new Exception(‘Error selecting database’);

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception(‘Error performing query ‘.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table=’default_table’){

$this->query(‘SELECT * FROM ‘.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// insert row

public function insert($params=array(),$table=’default_table’){

$sql=’INSERT INTO ‘.$table.’ (‘.implode(‘,’,array_keys($params)).’) VALUES (”.implode("’,'",array_values($params)).”)’;

$this->query($sql);

}

// update row

public function update($params=array(),$where,$table=’default_table’){

$args=array();

foreach($params as $field=>$value){

$args[]=$field.’=”.$value.”’;

}

$sql=’UPDATE ‘.$table.’ SET ‘.implode(‘,’,$args).’ WHERE ‘.$where;

$this->query($sql);

}

// delete one or multiple rows

public function delete($where=”,$table=’default_table’){

$sql=!$where?’DELETE FROM ‘.$table:’DELETE FROM ‘.$table.’ WHERE ‘.$where;

$this->query($sql);

}

}


Now the “MySQL” class includes another method, named “delete(),” whose task is to delete one or more rows from a MySQL table. Normally, when using the active record approach, this method would be tasked with removing one database row at a time, but in this case I took the liberty of expanding its functionality so that it can delete multiple records.

Having explained how the pertinent “delete()” method does its business, here’s a short code sample that shows it at work:


try{

// connect to MySQL and select a database

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

// insert new row into sample MySQL table

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

// delete row from sample MySQL table

$db->delete(‘id=4′,’users’);

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Here you have it. At this point, I’ve demonstrated with a simple MySQL handling class how to fetch, insert, update, and delete database rows by way of a slightly modified version of the active record pattern. As you saw before, it wasn’t necessary to write any SQL statements to perform these typical tasks.

Finally, feel free to tweak all of the code samples included in this tutorial, so you can get started building your own active record class!

Final thoughts

In this second installment of the series, you learned how to implement the active record approach within a basic MySQL abstraction class to update and delete records of a selected database table.

In the forthcoming part, I’ll be enhancing the functionality of this class so that it can perform conditional SELECT statements. Now that you’ve been told the topic that will be discussed in the next article, you won’t want to miss it!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan