A Matter Of Routine

If you're building a Web-based administration tool to interact with a database, there are some routine tasks that you quickly get used to coding: listing records, adding records, and updating or deleting records. Frameworks like the Zend Framework and CakePHP come with various built-in methods to help with this, and even if you're not using one of these frameworks, there are a number of tools that can make coding these functions a breeze.

Data objects, which provide an API for accessing and manipulating database tables, are one such tool. There are a number of implementations for data objects in PHP, most notably the popular PEAR DB_DataObject package. This tutorial focuses on one such implementation, the DB_OO2 package, showing you how it can significantly reduce your coding time when working with database tables.

Guild-ing The Lily

DB_OO2 works by allowing you to represent database tables as PHP objects, and providing pre-defined object methods to search, add, edit or delete data from the underlying tables. Not only does this approach save time, because the data object insulates you from the task of constructing and manipulating raw SQL queries, but it's also simpler because it's no longer necessary to be intimately aware of the structure and schema of the underlying database table to use it effectively.

Before we get started, it's important to state the assumptions this tutorial makes:

First, it assumes that you understand HTML, know the basics of PHP scripting, and are familiar with using SQL result sets in PHP.
Second, it assumes that you have an Apache/PHP development environment and a PostgreSQL RDBMS already set up. This is because the DB_OO2 package only includes support for PostgreSQL (at the moment).
Third, it assumes that you've managed to successfully install both the PEAR DB package and the DB_OO2 package. For your reference, the PEAR DB package is freely available from http://pear.php.net/package/DB, while the DB_OO2 package was developed by Blog.com and is currently maintained by Sergio Carvalho and can be downloaded from http://pear.sergiocarvalho.com/packages/package/DB_OO2/. (Note that DB_OO2 only works with PEAR DB; however, the next version of the package will include support for PDO and also for foreign key references).

This tutorial also makes use of an example database table. You can create and populate this table using the following SQL statements:

CREATE TABLE "Guilds" (
"GuildID" SERIAL PRIMARY KEY,
"GuildName" VARCHAR(255),
"GuildAttribute" VARCHAR(255)
);
INSERT INTO "Guilds" ("GuildName") VALUES ('Demon Divers');
INSERT INTO "Guilds" ("GuildName") VALUES ('Wicked Warriors');

Finally, it's worth mentioning at this point that this tutorial is intended mainly for users new to data objects and the DB_OO2 package. As such, it includes a general introduction to the package and how it works, but shies away from very complex examples. Once you've worked your way through this tutorial, you're sure to find many more sophisticated applications for DB_OO2 in your daily development.

Object Lesson

Let's begin with a simple example that showcases some of the power of DB_OO2: defining a data object that maps to the Guilds database table and using it to fetch data from the database:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// read Guild records
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $guild->fetchAll();
  while ($guild->next()) {
    printf("The %s are feared for their %s 
", $guild->getGuildName(), $guild->getGuildAttribute());  
  } 
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}
// close database connection
$dbh->disconnect();
?>

The first step in using DB_OO2 is to define objects that map to tables from the database. The listing above does this by defining a data object to represent the Guilds table, by invoking the factory() method with two parameters: a PEAR DB object that represents a connection to the PostgreSQL database, and the name of the table to use. The result of this is a Guild data object, which exposes various pre-defined methods to make interacting with the table easier:

The fetchAll() method retrieves all the records in the table;
The fetchById() methods retrieves a single record by its primary key;
The fetchByWhere() methods retrieves all records matching a specified condition;
The create() method adds a new record to the table;
The update() method updates an existing record in the table;
The delete() method removes a record from the table;

This particular listing demonstrates the fetchAll() method, which retrieves all the records in the table. A while() loop is then used to iterate over the record collection, printing each guild's name and primary attribute. Notice the getGuildName() and getGuildAttribute() methods: these are so-called "magic methods", which provide an easy way to retrieve the value of a specified field, simply by appending the field name to the get...() method.

Here's what the output looks like:

The Demon Divers are feared for their Dark Magic
The Wicked Warriors are feared for their Advanced Weaponry

It's worth noting also that, unlike PEAR's DB_DataObject package, DB_OO2 doesn't require a special database schema file with table and field information to be defined in advance of using the package; instead, DB_OO2 dynamically reads schema information from the database itself as needed.

Fetch And Carry

To retrieve a specific record, use the fetchById() method, which receives as arguments the primary key values and returns the record matching that key tuple. Here's an example:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// read Guild record with id 2
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $guild->fetchById(2);
  printf("The %s are feared for their %s <br/>", $guild->getGuildName(), $guild->getGuildAttribute());    
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

Here's the likely output:

The Wicked Warriors are feared for their Advanced Weaponry

It's also possible to return a subset of records matching a specified condition, with the fetchByWhere() method. This method accepts an SQL WHERE clause, as in the following example:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// read Guild records matching condition 
// condition: guild attribute contains 'c'
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $guild->fetchByWhere('"GuildAttribute" LIKE \'%c%\'');
  while ($guild->next()) {
    printf("The %s are feared for their %s <br/>", $guild->getGuildName(), $guild->getGuildAttribute());  
  } 
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

Adding It All Up

So that takes care of retrieving records from the database. Now, how about adding new records? It's actually pretty simple - all you need to do is call the data object's loadFromArray() method with an associative array of field-value pairs representing the record to be added, and then use the create() method to actually insert the record into the database table. There's no SQL code to write, and the entire process is fairly painless. Here's an example:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// add new Guild record
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $data = array(
    'GuildName'      => 'Grinning Ghouls',
    'GuildAttribute' => 'Telepathy'
  );
  $guild->loadFromArray($data);
  $guild->create();
  echo 'SUCCESS: Record added';
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

And now, when you look in the database after running this script, you should see the newly-added record:

test=# SELECT * FROM "Guilds";
 GuildID |    GuildName    |  GuildAttribute
---------+-----------------+-------------------
       1 | Demon Divers    | Dark Magic
       2 | Wicked Warriors | Advanced Weaponry
       3 | Grinning Ghouls | Telepathy
(3 rows)

An alternative way to accomplish the same thing is to manually set each field value with the...wait for it...setFieldValue() method before calling create(). Here's how:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// add new Guild record
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $guild->setFieldValue('GuildName', 'Grinning Ghouls');
  $guild->setFieldValue('GuildAttribute', 'Telepathy');
  $guild->create();
  echo 'SUCCESS: Record added';
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

Note that you don't need to set all of the table's fields. Fields not set will be filled with the default values present in the catalog. Additionally, auto-incrementing primary keys will be set automatically.

Making Changes

Every data object also comes with update() and delete() methods, which allow you to update or delete a record respectively. Updating is a two-step process: first, fetch the record using the fetchById() method and then, pass the update() method an associative array with the fields to be updated. Consider the following example, which illustrates by updating the record created in the previous example:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// update Guild record
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $guild->fetchById(3);  
  $guild->setFieldValue('GuildAttribute', 'Invisibility');
  $guild->update();
  echo 'SUCCESS: Record updated';
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

And now, when you look in the database, you should see the changed record:

test=# SELECT * FROM "Guilds";
 GuildID |    GuildName    |  GuildAttribute
---------+-----------------+-------------------
       1 | Demon Divers    | Dark Magic
       2 | Wicked Warriors | Advanced Weaponry
       3 | Grinning Ghouls | Invisibility
(3 rows)

Deleting a record follows a similar process, except that you will need to call the delete() method instead of the update() method. Here's an example:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// delete Guild record
try {
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $guild->fetchById(4);  
  $guild->delete();
  echo 'SUCCESS: Record deleted';
} catch (Exception $e) {
  die ('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

Putting It All Into Practice

Now that you know the theory, let's see how DB_OO2 can aid development in a more practical context, by building a simple database administration tool with it. Begin by adding a new table to the example database, to hold records of Members belonging to various Guilds, as follows:

CREATE TABLE "Members" (
"MemberID" SERIAL PRIMARY KEY,
"MemberName" VARCHAR(255),
"Experience" NUMERIC,
"Energy" NUMERIC,
"Stamina" NUMERIC,
"Agility" NUMERIC,
"GuildID" INTEGER REFERENCES "Guilds"("GuildID")
);

INSERT INTO "Members" ("MemberName", "Experience", "Energy", "Stamina", "Agility", "GuildID") values ('John X', 68, 12, 43, 11, 2);

INSERT INTO "Members" ("MemberName", "Experience", "Energy", "Stamina", "Agility", "GuildID") values ('Indigo Y', 64, 72, 35, 48, 1);

Note that the Members and Guilds tables are linked to each other via the 'GuildID' foreign key.

Now, how about writing some code to display these records?

<html>
  <head>
    <style type="text/css">
table {
  border-width: 1px 1px 1px 1px;
  border-spacing: 2px;
  border-collapse: collapse;
  border-color: gray gray gray gray;
  border-style: outset outset outset outset;
}
table td {
	border-width: 1px 1px 1px 1px;
  padding: 3px 3px 3px 3px;
  border-color: gray gray gray gray;
  border-style: outset outset outset outset;
}
    </style>
  </head>
  <body>
    <h2>List Members</h2>
<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

try {
  // get Member records
  $member = DB_OO2_Table::factory($dbh, 'Members');
  $guild = DB_OO2_Table::factory($dbh, 'Guilds');
  $member->fetchAll();
  
  // iterate over record set
  // retrieve each row as array
  if ($member->numRows() > 0) {
?>
      <table>
        <tr>
          <td>Name</td>
          <td>Guild</td>
          <td>Experience</td>
          <td>Agility</td>
          <td>Stamina</td>
          <td>Energy</td>
          <td colspan="2"></td>
        </tr>
<?php  
    while ($member->next()) {
      $guild->fetchById($member->getGuildID());
      $row = $member->castRowIntoArray();
?>
        <tr>
          <td><?php echo $row['MemberName']; ?></td>
          <td><?php echo $guild->getGuildName(); ?></td>
          <td><?php echo $row['Experience']; ?></td>
          <td><?php echo $row['Agility']; ?></td>
          <td><?php echo $row['Stamina']; ?></td>
          <td><?php echo $row['Energy']; ?></td>
          <td><a href="edit.php?id=<?php echo $row['MemberID']; ?>">edit</a></td>
          <td><a href="delete.php?id=<?php echo $row['MemberID']; ?>">delete</a></td>
        </tr>
<?php    
    }
?>
      </table>
<?php  
  } else {
    echo 'No records found!';  
  }
} catch (Exception $e) {
  die('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>
    </table>
    <a href="add.php">add a new record</a>
  </body>
</html>

Nothing particularly complicated here: the script generates a data object for the Members table, uses the fetchAll() method to retrieve all the Member records, and then displays them in a neat HTML table, with links to edit and delete each record and also add new records. A separate data object is also generated for the Guilds table, which uses the GuildID foreign key to retrieve the name of each Member's Guild.

Here's a sample of what the output might look like:

Add...

Clicking the 'add a new record' link directs the user to a new script, which generates an HTML form to add a Member. Here's the code:

<html>
  <head>
    <style type="text/css">
    </style>
  </head>
  <body>
    <h2>Add Member</h2>
    <?php 
    // include classes
    include_once 'DB.php';
    include_once 'DB_OO2/Table.php';
    
    // open database connection
    $dsn = 'pgsql://user@localhost/test';
    $dbh =& DB::connect($dsn);
    if (PEAR::isError($dbh)) {
      die('ERROR: ' . $dbh->getMessage());
    }
    
    // check if form has already been submitted
    // if not, display empty form
    if (!isset($_POST['submit'])) { 
      try {
        // get all Guild records
        $guild = DB_OO2_Table::factory($dbh, 'Guilds');
        $guild->fetchAll();
    ?>
    <form method="post" action="add.php">
      Name: <br/>
      <input type="text" name="name" value="" /><p/>
      Experience: <br/>
      <input type="text" name="experience" value="" size="2" /><p/>
      Agility: <br/>
      <input type="text" name="agility" value="" size="2" /><p/>
      Stamina: <br/>
      <input type="text" name="stamina" value="" size="2" /><p/>
      Energy: <br/>
      <input type="text" name="energy" value="" size="2" /><p/>
      Guild: <br/>
      <select name="guild">
      <?php 
      while ($guild->next()) {
        echo "<option value=\"" . $guild->getGuildID() . "\">" . $guild->getGuildName() . "</option>\n";  
      }
      ?>
      </select><p/>
      <input type="submit" name="submit" value="Add" />
    </form>
    <?php 
      } catch (Exception $e) {
        die('ERROR: ' . $e->getMessage());  
      }
    } else {
      // once form has been submitted
      // sanitize and validate input
      // note: illustrative only, use stronger validation in production
      $name = htmlentities(trim($_POST['name']));
      $guild = htmlentities(trim((int)$_POST['guild']));
      $experience = htmlentities(trim((int)$_POST['experience']));
      $energy = htmlentities(trim((int)$_POST['energy']));
      $stamina = htmlentities(trim((int)$_POST['stamina']));
      $agility = htmlentities(trim((int)$_POST['agility']));
      
      try {
        // create Member object from user input
        // save to database
        $member = DB_OO2_Table::factory($dbh, 'Members');
        $data = array(
          'MemberName' => $name,
          'GuildID' => $guild,
          'Experience' => $experience,
          'Agility' => $agility,
          'Stamina' => $stamina,
          'Energy' => $energy
        );
        $member->loadFromArray($data);
        $member->create($data);
        echo 'SUCCESS: Record added.';
  
      } catch (Exception $e) {
        die('ERROR: ' . $e->getMessage());  
      }

    }
    
    // close database connection
    $dbh->disconnect();
    ?>
  </body>
</html>

This script has two parts to it. The first part checks if the form has been submitted and, if not, generates an empty form to add a new Member. Here's an example of what the form looks like:

Notice the use of the fetchAll() method to generate a human-readable selection list of Guild names.

Once this form has been submitted, the second half of the script takes over. It sanitizes and validates the user input submitted through the form and then formats this input into an associative array. This array is then passed to the loadFromArray() method, and a new Member record is formulated and saved to the database via the create() method. You should then see this newly-added record in the Member list, as below:

... And Subtract

Clicking the 'delete' link directs the user to another script, and also passes along the unique Member identifier to that script through the URL GET method. The script then uses this identifier to locate and delete the corresponding record, using the fetchById() and delete() methods. Here's the code:

<?php
// include classes
include_once 'DB.php';
include_once 'DB_OO2/Table.php';

// check for record ID
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
  die('ERROR: No record identifier');
}

// open database connection
$dsn = 'pgsql://user@localhost/test';
$dbh =& DB::connect($dsn);
if (PEAR::isError($dbh)) {
  die('ERROR: ' . $dbh->getMessage());
}

// find and delete record
try {
  $member = DB_OO2_Table::factory($dbh, 'Members');
  $member->fetchById($_GET['id']);
  $member->delete();
  echo 'SUCCESS: Record deleted';
} catch (Exception $e) {
  die('ERROR: ' . $e->getMessage());  
}

// close database connection
$dbh->disconnect();
?>

And clicking the 'edit' link takes the user to yet another script, again passing along the Member identifier in the URL. This script generates a Web form pre-filled with the Member's current record, and invites the user to make changes to the record. On submission, the submitted input is validated, formatted into an array, and used to update the record via the update() method. Take a look at the code:

<html>
  <head>
    <style type="text/css">
    </style>
  </head>
  <body>
    <h2>Edit Member</h2>
    <?php
    // include classes
    include_once 'DB.php';
    include_once 'DB_OO2/Table.php';
    
    // open database connection
    $dsn = 'pgsql://user@localhost/test';
    $dbh =& DB::connect($dsn);
    if (PEAR::isError($dbh)) {
      die('ERROR: ' . $dbh->getMessage());
    }
    
    if (!isset($_POST['submit'])) {
        // check for record ID
      if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
        die('ERROR: No record identifier');
      }
      
      $id = htmlentities(trim($_GET['id']));
      try{
        // fetch all Guild records
        $guild = DB_OO2_Table::factory($dbh, 'Guilds');
        $guild->fetchAll();
        
        // fetch specified Member record
        $member = DB_OO2_Table::factory($dbh, 'Members');
        $member->fetchById($id);
      } catch (Exception $e) {
        die('ERROR: ' . $e->getMessage());
      }
    ?>
    <form method="post" action="edit.php">
      Name: <br/>
      <input type="text" name="name" value="<?php echo $member->getMemberName(); ?>" /><p/>
      Experience: <br/>
      <input type="text" name="experience" value="<?php echo $member->getExperience(); ?>" size="2" /><p/>
      Agility: <br/>
      <input type="text" name="agility" value="<?php echo $member->getAgility(); ?>" size="2" /><p/>
      Stamina: <br/>
      <input type="text" name="stamina" value="<?php echo $member->getStamina(); ?>" size="2" /><p/>
      Energy: <br/>
      <input type="text" name="energy" value="<?php echo $member->getEnergy(); ?>" size="2" /><p/>
      Guild: <br/>
      <select name="guild">
      <?php 
      while ($guild->next()) {
        echo "<option value=\"" . $guild->getGuildID() . "\"";
        echo ($guild->getGuildID() == $member->getGuildID()) ? " selected" : null;
        echo ">" . $guild->getGuildName() . "</option>\n";  
      }
      ?>
      </select>      
      <input type="hidden" name="id" value="<?php echo $member->getMemberID(); ?>" /><p/>
      <input type="submit" name="submit" value="Update" />
    </form>
    <?php  
    } else {
          // sanitize and validate input
          $name = htmlentities(trim($_POST['name']));
          $guild = htmlentities(trim((int)$_POST['guild']));
          $experience = htmlentities(trim((int)$_POST['experience']));
          $energy = htmlentities(trim((int)$_POST['energy']));
          $stamina = htmlentities(trim((int)$_POST['stamina']));
          $agility = htmlentities(trim((int)$_POST['agility']));
          $id = htmlentities(trim($_POST['id']));
          
          try {
            // update Member object from user input
            $member = DB_OO2_Table::factory($dbh, 'Members');
            $data = array(
              'MemberName' => $name,
              'GuildID' => $guild,
              'Experience' => $experience,
              'Agility' => $agility,
              'Stamina' => $stamina,
              'Energy' => $energy
            );
            $member->fetchById($id);
            $member->loadFromArray($data);
            $member->update();
            echo 'SUCCESS: Record updated.';
      
          } catch (Exception $e) {
            die('ERROR: ' . $e->getMessage());  
          }
                
    }
    
    // close database connection
    $dbh->disconnect();
    ?>
  </body>
</html>

And here is a screenshot of it in action:

As this example illustrates, using DB_OO2 can significantly reduce the drudge work of formulating and executing SQL queries for common data manipulation tasks. As such, it's a valuable addition to any programmer's toolkit, and one that you should definitely take some time out to experiment with. Play with it sometime, and see what you think!

Comments (Login to leave comments)

Monday, October 27, 2008

ABSTRACTION PLEASE.

12:39PM UTC · Steve-O [unregistered]

I don't understand why you write an article essentially over data abstraction but yet proceed to mash all of your data access logic and code into the presentation layer of your application. Yikes! :)

PHP5's object model received a complete overhaul for a reason...let's start using it. Seriously, at minimum, encapsulate that messy logic out into its own class(es), create an action event handler and keep your presentation layer to what it is suppose to be doing.

Friday, October 31, 2008

ARGH, REDUNDANCY

6:15PM UTC · Anonymous User [unregistered]

i know some people like it as a way of namespacing, but it just really bugs me when i see redundancy between table names and column names.

for instance, my guilds table would have the columns id, name, and attribute. not guildname. we know it's a guild name because it's in the guilds table. it's implicit. unless you're a fan of ambiguous natural joins and need PK and FK column names to match, it's an odd way of naming things.

same with objects. $guild->getName() is sufficient. $guild->getGuildName() is redundant.

Quick Links

Using Data Objects with PHP and DB_OO2