Review: conditional SELECT queries with the active record pattern - PHP

First I’d like to reintroduce the practical example created in the last installment of the series. It demonstrated how to build a MySQL abstraction class capable of performing both basic CRUD database operations and conditional SELECTS.

Originally, the aforementioned example, including the signature of the “MySQL” class looked like this:

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;

}

// fetch rows using 'WHERE' clause

public function fetchWhere($where,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$where);

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

}

}

try{

// connect to MySQL and select a database

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

// display users where ID > 5

$result=$db->fetchWhere('id>5','users');

foreach($result as $row){

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

}

/* displays the following

Amanda Bears amanda@domain.com
Jodie Foster jodie@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com
Jennifer Aniston jennifer@domain.com

*/

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

Now that you’ve examined the above hands-on example, you should be familiar with the logic implemented by the previous class to insert, update and delete rows in a specified MySQL table using the active record approach.

In addition to executing the task mentioned before, it’s worth noting that this sample class can run conditional SELECT queries by means of a highly-abstracted interface, in this case implemented via the “fetchWhere()” method.

At this point, and assuming that you've grasped how the “MySQL” class works, it’s time to continue extending its functionality. So, in accordance with the concepts deployed in the introduction, in the next few lines I’m going to show you how to adapt the class in question so it can work with SQL LIKE clauses.

This interesting topic will be discussed in the following section, so click on the link below and keep reading.