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

Completing the refactoring process of the MySQL abstraction class - PHP

In this group of articles you’ll 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

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.



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