Updating and Deleting Database Records with the Active Record Pattern - Updating database rows with the active record pattern (
Page 3 of 4 )
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.
