The LIKE Clause and the Active Record Pattern - Review: conditional SELECT queries with the active record pattern (
Page 2 of 4 )
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.
