Home arrow PHP arrow Page 3 - The mysqli Extension and the Active Record Pattern

Finish updating the MySQL class - PHP

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.

TABLE OF CONTENTS:
  1. The mysqli Extension and the Active Record Pattern
  2. Getting started using the mysqli PHP extension
  3. Finish updating the MySQL class
  4. The sample MySQL class in action
By: Alejandro Gervasio
Rating: starstarstarstarstar / 4
April 14, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



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