Completing the refactoring process of the MySQL abstraction class - PHP

True to form, refactoring the remaining methods of the previous “MySQL” abstraction class is only a question of using its new properties to dynamically create the corresponding queries. In this case, I’m going to show you how to modify the “fecthWhere(),” “fetchLike()” and “fetchLimit()” methods that you saw before, so they can be used to fetch database rows from a selected database table using the respective “WHERE,” “LIKE” and “LIMIT” clauses.

That being explained, here’s the complete signature of this sample class, including all the modifications that I mentioned above:

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;

}

}

As I said before, the “fetchWhere(),” “fetchLike()” and “fetchLimit()” methods have been slightly modified, and now they use internally the private properties of the class to dynamically arm the corresponding SQL queries, in accordance with the type of clause that must be executed.

Provided that you now understand how this refactoring process has been accomplished, it’s time to set up a final practical example, where this improved version of the “MySQL” class will be used for inserting, updating and deleting rows of a sample MySQL table.

This concluding example will be created in the section to come. So go ahead and read the new few lines.