Running Conditional Select Statements with the Active Record Pattern - Performing conditional SELECTS through the active record pattern (
Page 3 of 4 )
In reality, providing the previous “MySQL” class with the ability to perform conditional SELECT statements using the active record pattern only involves coding a brand new method, which will carry out this task behind the scenes. Basically, the method will look very similar to the “fetch()” that you learned before, except that in this case, it’ll append a WHERE clause to the end of the SELECT command.
However, the best way for you to understand how this method will work is by showing you its corresponding signature, which looks like this:
// 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;
}
Didn’t I tell you that this new method would be very simple to grasp? And if you study its implementation, you’ll have to agree with me on this. But let me explain how it works, in case you have some trouble understanding its logic.
As you can see, it looks nearly identical to the “fetch()” method, except that this one aggregates a WHERE clause to the end of the SQL query, in this way constructing the conditional SELECT.
Of course, if you examine in detail how this method functions, then you’ll realize that it can be improved, but its current functionality is more than enough to demonstrate how simple it is to define a method like this.
At this stage, I’m sure that you now understand how the above “fetchWhere()” method does its thing, so it’s time to list the enhanced version of the “MySQL” class, this time including this method. 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;
}
// 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);
}
}
At this point, the “MySQL” class is indeed quite useful, since it’s capable not only of performing CRUD database operations, but also has the ability to execute conditional SELECTS. Even so, there’s one thing that remains undone; it’s necessary to develop an example that shows the actual functionality of the “fecthWhere()” method.
The last section of this tutorial will be dedicated to setting up such an example, so click on the link that appears below and keep reading. We’re almost finished!
