The mysqli Extension and the Active Record Pattern - Getting started using the mysqli PHP extension (
Page 2 of 4 )
As I anticipated in the introduction, my goal here consists of rewriting the MySQL abstraction class developed in preceding articles of the series by using the enhanced “mysqli” PHP extension. So, based on this idea, I’m going to start this process by listing the old signature of this class, so you’ll be able to compare it with the newer version that I plan to create later on.
Having explained that, here’s how this sample “MySQL” class looked originally:
class MySQL{
private $result;
private $select='SELECT * FROM ';
private $where=' WHERE ';
private $limit=' LIMIT ';
private $like=' LIKE ';
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($this->select.$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).$this->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.$this->where.$where;
$this->query($sql);
}
// fetch rows using 'WHERE' clause
public function fetchWhere($where,$table='default_table'){
$this->query($this->select.$table.$this->where.$where);
$rows=array();
while($row=$this->fetchRow()){
$rows[]=$row;
}
return $rows;
}
// fetch rows using 'LIKE' clause
public function fetchLike($field,$like,$table='default_table'){
$this->query($this->select.$table.$this->where.$field.$this->like.$like);
$rows=array();
while($row=$this->fetchRow()){
$rows[]=$row;
}
return $rows;
}
// fetch rows using 'LIMIT' clause
public function fetchLimit($offset=1,$numrows=1,$table='default_table'){
$this->query($this->select.$table.$this->limit.$offset.','.$numrows);
$rows=array();
while($row=$this->fetchRow()){
$rows[]=$row;
}
return $rows;
}
// add single quotes to query parameters
private function addQuotes($value){
return '''.$value.''';
}
}
Having listed the entire signature of the above abstraction class, which uses the old MySQL library to do its thing, it’s time to start rewriting its core methods by means of the improved “mysqli” PHP extension.
Now look at the improved version of this class, which implements its most basic methods by using the aforementioned extension. Here it is:
class MySQL{
private $mysqli;
private $result;
private $select='SELECT * FROM ';
private $where=' WHERE ';
private $limit=' LIMIT ';
private $like=' LIKE ';
public function __construct($host='localhost',$user='user',$password='password',$database='database'){
// connect to MySQL and select database
$this->mysqli=new mysqli($host,$user,$password,$database);
if(mysqli_connect_errno()){
throw new Exception('Error connecting to MySQL: '.$this->mysqli->error);
}
}
// run SQL query
public function query($query){
if(!$this->result=$this->mysqli->query($query)){
throw new Exception('Error running SQL query: '.$this->mysqli->error);
}
}
// fetch one row
public function fetchRow(){
while($row=$this->result->fetch_assoc()){
return $row;
}
return false;
}
// fetch all rows
public function fetchAll($table='default_table'){
$this->query($this->select.$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 “MySQL” class now implements its CRUD methods by utilizing the enhanced “mysqli” PHP extension. Indeed, at first sight it looks pretty similar to the old-fashioned version, but if you study its signature more closely, you’ll see that it employs internally a couple of improved methods that come bundled with this newer MySQL library.
At this moment, you hopefully understand how the CRUD methods of the previous MySQL abstraction class have been rewritten by using the “mysqli” extension. Therefore, it’s time to complete this updating process.
In the section to come I’ll be showing you how to recode the remaining methods of the class, a process that you’ll grasp with minor hassles.
Click on the link that appears below and keep reading.
/ 4 
