The mysqli Extension and the Active Record Pattern

The active record pattern allows you to access records in a database by means of a unified interface, generally composed of a few data mapper objects. However, as with other popular design patterns, there are several methodologies for implementing it within an object-oriented application, all of which can be used to obtain similar results. In this group of articles, I demonstrate how to progressively build a MySQL abstraction class that uses the active record approach for performing CRUD operations (Create, Retrieve, Update, Delete) on the records of a selected database table.

Introduction

Naturally, if you’ve read the six preceding articles (this is the seventh and concluding part) then by now you’re pretty familiar with creating a class like this. In those articles I explained how to develop a sample MySQL-accessing class that was capable of inserting, updating and deleting records, as well as performing conditional SELECT statements against a specified MySQL table.

In this case, the class accomplished all of these database tasks by way of a slightly modified version of the active record pattern, since no mapper objects were used here. Instead, it implemented a few straightforward methods for executing dynamic queries through a highly-simplified API.

We’re not quite finished with this quick overview on accessing MySQL tables with the active record pattern yet, because there’s one step that remains undone. What is it? Well, as you’ll surely recall, I built this class by using the old MySQL library included with the PHP distribution, which is good and efficient.

But it’s worth making the effort to see how this sample class can be rewritten by using the newer, revamped “mysqli” extension. Thus, this last episode will be dedicated exclusively to doing this, so you can have at your disposal an enhanced versions of the class.

Are you ready to tackle this final chapter of the series? Then go ahead and start reading now!

{mospagebreak title=Getting started using the mysqli PHP extension}

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.

{mospagebreak title=Finish updating the MySQL class}

In the prior section, you saw for yourself how simple it is to rewrite the CRUD methods of the “MySQL” class by utilizing the “mysqli” extension. Indeed, this updating process consists of using the appropriate syntax required for the extension, rather than modifying the implementation of the methods themselves, and this will also be true when it comes to rewriting the rest of the class.

To demonstrate the veracity of this concept, below I’ve written the entire improved version of the class, this time including the methods that needed to be updated:


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

}

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

}

}


That wasn’t rocket science, was it? As shown before, the “MySQL” class now implements all of its methods via the “mysqli” extension, which turns it into a more efficient piece of software.

Nevertheless, this updating process would be rather incomplete if I didn’t show you a concrete example that demonstrates how this improved version of the class can be used to access a sample MySQL table.

Therefore, the final section of this article will be exclusively dedicated to setting up this example, which hopefully will be an appropriate epilogue for this introductory guide on using the active record approach with PHP.

Go ahead and read the next few lines. We’re almost done!

{mospagebreak title=The sample MySQL class in action}

If you’re anything like me, then it’s quite possible that you may want to see how the previous “MySQL” class can be put to work after rewriting all of its methods. Therefore, in the next few lines I included a number of code samples to demonstrate how to use the class for performing some basic database operations on the same “users” MySQL table utilized in previous tutorials.

In case you don’t recall how this sample table was populated, here it is:



Now that there’s a MySQL table to work with, here are the code samples that utilize it:


try{

// connect to MySQL and select a database

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

// insert new row

$db->insert(array(‘firstname’=>’Kate’,’lastname’=>’Johanson’,’email’=>’kate@domain.com’),’users’);

// update row

$db->update(array
(‘firstname’=>’Kathleen’,’lastname’=>’Johanson’,’email’=>’kate@domain.com’),’id=1′,’users’);

// delete row

$db->delete(‘id=1′,’users’);

// display all users

$result=$db->fetchAll(‘users’);

foreach($result as $row){

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

}


/* displays the following


Alejandro Gervasio alejandro@domain.com
John Doe john@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
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

*/

// 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

*/

// display users where first name contains the ‘a’ character

$result=$db->fetchLike(‘firstname’,"’%a%’",’users’);

foreach($result as $row){

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

}

/* displays the following


Alejandro Gervasio alejandro@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com


*/

// display users with the LIMIT clause

$result=$db->fetchLimit(2,4,’users’);

foreach($result as $row){

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

}

/* displays the following

Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com

*/

}


Undoubtedly, the set of examples listed above clearly illustrate how the updated “MySQL” class can be used to perform different queries against a simple MySQL table, through a simple interface.

Even though the class implements a slightly different version of the active record pattern, the level of SQL abstraction achieved is more than acceptable, considering that it’s been coded for only illustrative purposes.

Feel free to modify all the code samples included into this article, so you can improve your existing skills in using the active record approach with PHP.

Final thoughts

Now that we’ve come to the end of this series, there’s a couple of options that you can try: either you can code your own active record class based on the examples developed in these tutorials, or you can use one included with a third-party framework. In either case, you’ll be armed with a solid background in how to use this pattern to access your databases.

See you in the next PHP web development tutorial!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan