Updating database rows with the active record pattern - PHP

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.