Building a simple data mapper in PHP - PHP

A good way to start explaining how to use the active record pattern in PHP consists of creating a quick and dirty data mapper class. This class will be tied to a specified MySQL database table to select, insert, update and delete its rows, without having to code any SQL queries.

That being said, suppose for a moment that there’s a sample “users” table that will be accessed via a data mapper class. According to this scenario, the primitive signature of this class might look similar to this:

class User{

private $firstName;

private $lastName;

private $email;

private $table='users';

public function __construct(){}

// set first name

public function setFirstName($firstName){

$this->firstName=$firstName;

}

// set last name

public function setLastName($lastName){

$this->lastName=$lastName;

}

// set email

public function setEmail($email){

$this->email=$email;

}

// fetch row

public function fetch($id){

if(!$row=mysql_query("SELECT * FROM $this->table WHERE id='$id'")){

throw new Exception('Error fetching row');

}

return $row;

}

// insert row

public function insert(){

if(!mysql_query("INSERT INTO $this->table (firstname,lastname,email) VALUES ($this->firstName,$this->lastName,$this->email)")){

throw new Exception('Error inserting row');

}

}

// update row

public function update($id){

if(!mysql_query("UPDATE $this->table SET firstname='$this->firstName,lastname=$this->lastName,email=$this->email WHERE id='$id'")){

throw new Exception('Error updating row');

}

}

// delete row

public function delete($id){

if(!mysql_query("DELETE FROM $this->table WHERE id='$id'")){

throw new Exception('Error deleting row');

}

}

}

As you can see, the above “User” class implements a set of straightforward methods for selecting inserting, updating and deleting rows from a fictional “users” MySQL database table. Notice that each of these operations works with only one row at time, in this way behaving like a simple interface that permits you to access data in the table.

Now that you hopefully understood how the previous data mapper class does its thing, here’s a basic example that demonstrates its usage in a concrete case. Take a look at the code sample below:

try{

$user=new User();

// set first name

$user->setFirstName('Alejandro');

// set last name

$user->setlastName('Gervasio');

// set email

$user->setEmail('alejandro@domain.com');

// insert row

$user->insert();

// set first name

$user->setFirstName('Alejandro');

// set last name

$user->lastName('Gervasio');

// set email

$user->setEmail('alex@domain.com');

// update row

$user->update(1);

// delete row

$user->delete(1);

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

As shown above, the previous sample class uses the active record pattern to insert, update and delete a single row from the corresponding “users” MySQL table. It's easy to see here how the pattern permits us to perform these tasks in an intuitive manner, even when no SQL statements have been explicitly coded.

So far, everything looks good. At this point, you've learned how to build a simple class with PHP 5 which utilizes the active record pattern for interacting with a selected MySQL table. However, as I expressed at the beginning, it’s also possible to achieve a decent level of SQL abstraction without using a data mapper class like the one shown before.

In fact, it’s perfectly feasible to create a simple database abstraction class that performs selects, updates, deletions, and so forth, without directly writing any SQL clauses. And this is exactly what I’m going to do in the next section, so you can understand more clearly how the active record pattern can be applied by using a slightly different approach.

In order to learn how this brand new PHP class will be defined, please click on the link that appears below and read the next few lines.