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

To start refactoring the sample “MySQL” class built in the previous tutorial, the first step I’m 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, it’s time to begin refactoring some core methods to avoid business logic duplication. Thus, here’s 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, it’s 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, it’s also necessary to modify the implementation of the remaining methods to complete the refactoring process.

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