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

Updating 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

Any decent class that uses the active record pattern to interact with a database must be capable of updating its records, and the one that I'm building here isn't an exception. Therefore, I'm going to define a brand new method within the previous "MySQL" class, called "update()." It will be responsible, obviously, for updating a single row in a specified MySQL table.

Now that I have explained how this method will work, please take a look at the enhanced signature of the "MySQL" class, which incorporates the "update()" method mentioned above. 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;

}

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

}

}


As shown above, the previous "MySQL" class now includes an "update()" method. It's useful for updating an existing record within a specific MySQL table. Also, it's worthwhile to mention here that the method in question accepts a "$where" input argument, which will be used internally for constructing the corresponding conditional UPDATE statement. Not too difficult to understand, right?

Well, assuming that you already grasped the logic that drives the prior "update()" method, please pay attention to the following code sample, which shows how to update a record of the sample "users" MySQL table created in the first tutorial:


try{

// connect to MySQL and select a database

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

// insert new row into sample MySQL table

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

// update row of sample MySQL table

$db->update(array('firstname'=>'Kathleen','lastname'=>
'Johanson','email'=>'kate@domain.com'),'id=4','users');

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


See how simple it is to update an existing database record by using the active record approach? I bet you do! And naturally, one of the major advantages in using it is that no SQL statements have to be written to perform this specific operation.

Okay, at this stage, the previous "MySQL" class has been provided with the capability for fetching, inserting and updating database rows. So what's next? Yes, you guessed right. To extend the functionality of the class even more, it's necessary to define another method that allows us to delete records from a specified MySQL table.

Therefore, to learn how this brand new method will be implemented, click on the link that appears 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: