PHP DevCenter
oreilly.comSafari Books Online.Conferences.

advertisement


Simplify Business Logic with PHP DataObjects
Pages: 1, 2, 3

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.

Pages: 1, 2, 3

Next Pagearrow




Valuable Online Certification Training

Online Certification for Your Career
Earn a Certificate for Professional Development from the University of Illinois Office of Continuing Education upon completion of each online certificate program.

PHP/SQL Programming Certificate — The PHP/SQL Programming Certificate series is comprised of four courses covering beginning to advanced PHP programming, beginning to advanced database programming using the SQL language, database theory, and integrated Web 2.0 programming using PHP and SQL on the Unix/Linux mySQL platform.

Enroll today!


Sponsored by: