Review: performing CRUD operations through the active record approach - PHP

You may wonder how we're going to provide the MySQL abstraction class built in the previous article with the ability for performing conditional SELECT statements using the active record pattern. Before I proceed to discuss that particular topic, however, it would be useful to list the signature of this class in its current state. As I mentioned, it was capable of performing only CRUD tasks (Create, Remove, Update, Delete).

That being said, here’s how this MySQL-accessing class looked originally:

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);

}

}

As illustrated above, the previous “MySQL” class implements a few straightforward methods for fetching, inserting, updating and deleting rows in a specified MySQL table. As I said before, all of these operations are performed without the need to directly code any SQL commands, as you can see clearly in the code sample above.

Now that you've recalled how this basic “MySQL” class was defined, here’s a short example that shows how it can be used for consecutively adding and deleting one single row from a fictional “users” MySQL table:

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();

}

Undoubtedly, the above hands-on example demonstrates in a nutshell how to use the “MySQL” class for inserting a new row into a sample “users” MySQL table, and how to delete it later on, by means of its respective “insert()” and “delete()” methods. This process is actually very simple to follow.

Well, at this point you hopefully recalled how to use the previous class to perform CRUD operations via the active record approach. However, as I mentioned in the introduction, it’s perfectly possible to incorporate many other methods into the class to extend its existing functionality.

Bearing this idea in mind, in the section to come I’ll be adding a brand new method to the sample MySQL class. In doing so, it’ll be able to perform simple conditional SELECT queries, without coding any explicit SQL statements.

To learn the full details of how this new method will be defined, please click on the link that appears below and read the next few lines.