Simplify Business Logic with PHP DataObjects
Data Access Versus Database Abstraction
Let's take a moment to discuss data access layer vs. database abstraction
layer. As the introduction explained, these are two different things. You can
use one or the other, or even both for the best overall flexibility.
The database abstraction layer hides the RDBMS running in the background.
If you're smart and careful about how you write your SQL, you can easily switch
from one database server to another, without changing any function calls or
SQL. Pear::DB does this quite
nicely.
A data access layer, on the other hand, hides the underlying table
structure. By writing a data access layer to represent your tables, you can
interact with the data in your tables without using any SQL in the business
layer (where all the functional code lives) of your application. DataObjects
are a good choice for the data access layer, as they have a direct relationship
to tables, so you can reuse them across your whole application without
modifying them.
Before DataObjects, my data layer consisted of classes that encapsulated and
grouped queries based on functionality; for example, all queries related to
registering and managing a user's login. The problem with this kind of layer is
that different bits of unrelated functionality had similar queries. To log
someone into the application, I need a user record. I also need that record to
display an "edit user info screen." I had to to copy and paste the same query
into two separate classes, due to the architecture of the code. With DataObjects, if I need a user's record, I use the DataObject that represents that
table, regardless of where I am in the code.
You can use database abstraction and data access layers together. Our
DataObjects simply use Pear::DB instead of PHP's native database functions.
Most of the time, I forego using Pear::DB, as I know the chances of changing
the underlying RDBMS is next to nil. When I know there is a good chance of the
RRDBMS changing within the next five years, I'll use Pear::DB just to be safe.
Otherwise, using PHP's native DB functions are faster to code, and execute
faster. Pear::DB adds another layer of abstraction that results in a slight
slowdown in code execution.
Inserting Rows
Retrieving records is a good start, but we need more. We need to add new
records to the table, so let's add another method to our DataObject that will
perform an INSERT [file
source]:
public function insert()
{
$sql = "INSERT INTO
User
SET
firstName='" . mysql_escape_string($this->firstName) . "',
lastName='" . mysql_escape_string($this->lastName) . "',
email='" . mysql_escape_string($this->email) . "'";
mysql_query($sql);
$this->userId = mysql_insert_id();
}
Isn't this clever? You may wonder where the data to insert comes from.
That data needs to be in the data object before we call this, as we
don't pass on the data we want to insert as parameters of the
insert() method. The code retrieves the last insert_id and stores
it within the DataObject so the object is now usable for related inserts on
other tables. Here's how to use this new method:
<?php
include_once('class-DO_User.php');
$user = new DO_User();
$user->firstName = 'Jane';
$user->lastName = 'Doe';
$user->email = 'jane.doe@example.com';
$user->insert();
?>
<html>
<head>
<title>INSERT Example</title>
</head>
<body>
<p>The user was added to the User table.
The userId is: <?=$user->userId?></p>
</body>
</html>
This example inserts a new record into the User table. We
create the DataObject, set the field values, and then call
insert(). Nice and easy. We can also use both the
get() and insert() methods together to copy a record.
It doesn't end there; we could also make a small change to the record before we
do a copy. Want to change the email address? Here's how:
<?php
include_once('class-DO_User.php');
$user = new DO_User();
// Again just using a literal int for now.
$user->get(5);
// Change the email address in the DataObject
// NOTE: This doesn't affect the DB at all,
// just the value in the DataObject
$user->email = 'jdoe@example.com';
// Next, we call insert() to create a NEW record in the User table
$user->insert();
?>
<html>
<head>
<title>Copy Row INSERT Example</title>
</head>
<body>
<p>The user was copied to the User table.
The userId is: <?=$user->userId?></p>
</body>
</html>
I'm excited just writing this! Hopefully you're starting to see how easy it
is to interact with your database when using DataObjects.
Updating Rows
Retrieving a record, changing a few values, and inserting it as a new record
can be valuable. More often, you'll want to update that record with
the changes. Let's add an update() method to our DataObject [file source]:
public function update()
{
$sql = "UPDATE
User
SET
firstName='" . mysql_escape_string($this->firstName) . "',
lastName='" . mysql_escape_string($this->lastName) . "',
email='" . mysql_escape_string($this->email) . "'
WHERE
userId=" . mysql_escape_string($this->userId);
mysql_query($sql);
}
This is just as clever as the insert(). Once again, the data
we're working with needs to be in the DataObject before this point. This method
simply uses that existing data to perform the update. Here's how to use it:
<?php
include_once('class-DO_User.php');
$user = new DO_User();
// Again just using a literal int to get a row.
$user->get(5);
// Change the email address
$user->email = 'janedoe@example.com';
// Perform the update
$user->update();
?>
<html>
<head>
<title>UPDATE Example</title>
</head>
<body>
<p>The user updated. The userId is: <?=$user->userId?></p>
</body>
</html>
When I first looked into DataObjects, I didn't see the value in them. It
seemed like a lot of setup in coding of all the DataObjects, with little return. It
wasn't until I started using them that their value truly started to show. Can
updating a user's email address in the DB be any easier than that last example?
Not only is the code here compact, but it's highly readable and clean -- two
things I like to see in code.
Deleting Rows
That covers three of the four fundamental database tasks. The advantages of
DataObjects should be obvious at this point. Let's consider the
DELETE method by adding delete() [file source]:
public function delete()
{
$sql = "DELETE FROM
User
WHERE
userId=" . mysql_escape_string($this->userId);
mysql_query($sql);
}
Use delete() in the same way as you use
update(). Find the row you want to delete
($user->get(5)), then call delete
($user->delete()). Once again, the primary key of the record
must already exist in the DataObject.
