Updating and Deleting Database Records with the Active Record Pattern - Deleting database rows with the active record approach (
Page 4 of 4 )
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!
