Home arrow PHP arrow Page 2 - Updating and Deleting Database Records with the Active Record Pattern

Review: fetching and inserting database rows with the active record pattern - PHP

Regardless of its rather intimidating name, the active record pattern is actually quite simple to implement with PHP when developing database-driven applications. Indeed, this pattern permits you to achieve a high level of abstraction when performing typical operations, such as insertions, updates and deletions of records, since all of these tasks are handled transparently by data mappers objects or a data access class.

TABLE OF CONTENTS:
  1. Updating and Deleting Database Records with the Active Record Pattern
  2. Review: fetching and inserting database rows with the active record pattern
  3. Updating database rows with the active record pattern
  4. Deleting database rows with the active record approach
By: Alejandro Gervasio
Rating: starstarstarstarstar / 3
March 10, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Before I show you how to use the MySQL abstraction class created in the first article to update and delete database rows, let's recall very quickly how this class originally looked.

Here's the class' complete signature, along with a couple of additional examples that show how to fetch all the rows from a sample "users" MySQL table and how to insert new records:


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;

}

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

}

}


try{

// connect to MySQL and select a database

$db=new MySQL('host','user','password','mydatabase');

$result=$db->fetchAll('users');

// fetch all rows from sample MySQL table

foreach($result as $row){

echo $row['firstname'].' '.$row['lastname'].' '.$row['email'].'<br />';

}

// insert new row into sample MySQL table

$db->insert(array('firstname'=>'Kate','lastname'=>'Johanson','email'=>'kate@domain.com'),'users');

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Undoubtedly, understanding how the above "MySQL" class works isn't rocket science, right? As you'll probably recall, other than performing "conventional" queries against a selected database, the class includes two relevant methods, called "fetchAll()" and "insert()" respectively, which use the active record approach for fetching all the records from a specified MySQL table and for adding new ones as well.

In this particular case, the implementation of these handy methods permits us to encapsulate the logic required for constructing SELECT and INSERT statements, in this manner achieving a good level of SQL abstraction.

All in all, at this point I'm sure that you've grasped how the prior "MySQL" class functions. Thus, it's time to see how to expand the functionality of the class. In its current state, it's capable of fetching and inserting rows, so next I'll define a new method to update a row within a MySQL table.

However, to see how this brand new method will be created, you'll have to click on the link below and read the following section.



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