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

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.