Deleting database rows with the active record approach - PHP

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!