Home arrow PHP arrow Page 2 - Refactoring the MySQL Abstraction Class with the Active Record Pattern

Refactoring the MySQL sample class: modifying methods that perform CRUD operations - PHP

In this group of articles youll be provided with an approachable guide to building a database accessing class that will use the programming model imposed by the active record pattern for performing raw CRUD operations on a group of selected MySQL tables, and for fetching database records by using the WHERE, LIKE and LIMIT clauses as well. This is the sixth part of a seven-part series.

TABLE OF CONTENTS:
  1. Refactoring the MySQL Abstraction Class with the Active Record Pattern
  2. Refactoring the MySQL sample class: modifying methods that perform CRUD operations
  3. Completing the refactoring process of the MySQL abstraction class
  4. Manipulating database records using the improved version of the MySQL class
By: Alejandro Gervasio
Rating: starstarstarstarstar / 4
April 07, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

To start refactoring the sample MySQL class built in the previous tutorial, the first step Im going to take will consist of modifying its most basic methods; that is, the ones that perform CRUD operations. On the other hand, the methods tasked with connecting to the server and selecting a database will remain the same.

But before I proceed to introduce these changes to the class, it would be convenient to recall how it looked originally. 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;

}

// fetch rows using 'LIKE' clause

public function fetchLike($field,$like,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$field.' LIKE '.$like);

$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 'LIMIT' clause

public function fetchLimit($offset=1,$numrows=1,
$table='default_table'){

$this->query('SELECT * FROM '.$table.' LIMIT '.$offset.','.$numrows);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

}


Having listed the complete signature of the MySQL class as it was built progressively in prior tutorials of the series, its time to begin refactoring some core methods to avoid business logic duplication. Thus, heres the brand new definition of this sample class, this time including a few important modifications. Take a look at it, please:


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

}

}


As shown before, the MySQL class now implements its CRUD methods much more intelligently. Each query is dynamically constructed by using the corresponding SQL statements, which have been stored previously as class properties. In doing so, its possible to easily define many other methods without having to use MySQL-related functions over and over again.

So far, so good. At this stage, I showed you how to specifically refactor the methods of the above MySQL class that are charged with performing CRUD tasks on a specified database table. Yet, as you might have guessed, its also necessary to modify the implementation of the remaining methods to complete the refactoring process.

Therefore, in the following section Ill be showing you how to modify the rest of the class. But naturally, to see how those changes will be accomplished, youll have to click on the link below and keep reading.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: