Home arrow PHP arrow Page 4 - Updating and Deleting Database Records with the Active Record Pattern

Deleting database rows with the active record approach - PHP

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.

TABLE OF CONTENTS:
  1. Updating and Deleting Database Records with the Active Record Pattern
  2. Review: fetching and inserting database rows with the active record pattern
  3. Updating database rows with the active record pattern
  4. Deleting database rows with the active record approach
By: Alejandro Gervasio
Rating: starstarstarstarstar / 3
March 10, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



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