English version  | Version française
Database frontend with PEAR DataGrid and DataObject

Structures_DataGrid and DB_DataObject are two PEAR components, which can be connected together, to easily display the data contained in a SQL table, and let the user sort and page through it.

By cleverly using these two components, and with minimal typing, I will try and show you how you can bypass the usual steps of parsing GET parameters, performing SQL requests, and generating HTML content.

Requirements :

I. What binding is, and what it can do

Let's start with a short and self-explanatory example:

<?php
class DataObject_Fruits extends DB_DataObject 
{
    var 
$__table "fruits";
    var 
$id;
    var 
$name;
    var 
$stock;
    var 
$price;
}

$dataobject = new DataObject_Fruits();
$datagrid =& new Structures_DataGrid(10);
$datagrid->bind($dataobject); // the Magic
$datagrid->render();
?>

The code above will turn the following SQL table into a formatted and sortable data table, or datagrid. Just try it, click on some of the table headers, for example on the price. A redundant click will perform a reverse sort :

SQL table (Download)

mysql> select * from fruits limit 5;
+----+----------+-------+-------+
| id | name     | stock | price |
+----+----------+-------+-------+
|  1 | Apples   |   260 |  1.40 |
|  2 | Apricots |   420 |  2.30 |
|  3 | Bananas  |   240 |  0.80 |
|  4 | Cherries |   420 |  3.50 |
|  5 | Coconut  |   340 |  0.80 |
+----+----------+-------+-------+
5 rows in set (0.00 sec) 
Structures_DataGrid output

So... What's so magic ? Well, there's :

  1. No need to type any HTML code : it is generated by Structures_DataGrid
  2. No need to parse the GET parameters : they are automatically handled by Structures_DataGrid
  3. No need to write SQL statements : DB_DataObject is responsible for this.

In three words, I call this feature : Just Type Bind. Actually the bind() method can handle many types of data sources, which it automatically recognizes : XML, DB_Result, etc... That's the other side of its "Magic", but in this tutorial I will focus on DB_DataObject.

II. A first complete example

In the previous section I have omitted a few lines, for clarity reasons. Indeed, before instantiating a DataObject or a DataGrid, we need to include a few files, and set up the database. The following is the complete and commented source. No HTML, no SQL, no GET. The code is less than 20 lines :

<?php
/* Includes */    
require_once "PEAR.php";
define("DB_DATAOBJECT_NO_OVERLOAD",true); /* This is needed for some buggy versions of PHP4 */
require_once "DB/DataObject.php";
require_once 
"Structures/DataGrid.php";    

/* Database and DataObject setup */
$dataobjectOptions = &PEAR::getStaticProperty("DB_DataObject","options");
$dataobjectOptions["database"] =  "mysql://username:password@host/database";
$dataobjectOptions["proxy"] = "full";

class 
DataObject_Fruits extends DB_DataObject 
{
    var 
$__table "fruits";
    var 
$id;
    var 
$name;
    var 
$stock;
    var 
$price;
}

/* Instantiate */
$dataobject = new DataObject_Fruits();
$datagrid =& new Structures_DataGrid(10); /* 10 rows per table */

/* Bind */
$datagrid->bind($dataobject);

/* Output */
$datagrid->render();
?>

III. Fields and Labels

Let's now try and improve our output. The 'id' field is not interesting for the end-user, it is an internal reference, and we do not need to include a column for it. Additionally, the field names are not user-friendly, we should use proper column labels. The bind() method supports some options for all this :

<?php

/* Required in order to use the "fields" and "labels" options */
$datagridOptions["generate_columns"] = true

/* The fields we want to display */
$datagridOptions["fields"] = array ("name""stock""price");

/* Translate the fields names into user-friendly labels */
$datagridOptions["labels"] = array (
    
"name" => "Product Name"
    
"stock" => "Quantity in Stock"
    
"price" => "Price (&euro;)"
);

/* Pass these options at binding time */
$datagrid->bind($dataobject$datagridOptions);

/* And render */
$datagrid->render();
?>

It should look better :

IV. Paging

Structures_DataGrid also supports paging the data. As with sorting, the user's GET requests are handled transparently, and some HTML links are generated. To use this feature we need to access one of the internal layers of Structures_DataGrid : the Renderer.

<?php
$datagrid
->bind($dataobject$datagridOptions);

/* Get a reference to the Renderer object */    
$renderer =& $datagrid->getRenderer();

/* Get and output HTML links */
$pagingHtml $renderer->getPaging();
echo 
"<p>Pages : $pagingHtml</p>";

/* Render the table */
$datagrid->render();
?>

Now we can move around :

V. Layout customization

Nice code is something developers like, but end-users and designers won't get convinced unless you make it look fancy. For maximum flexibility you may want to write your own Renderer, but that is out of the scope of this tutorial. Don't worry though, the default Structures_DataGrid Renderer offers several methods to customize the table layout.

In the example below we only use these methods to assign CSS classes to our elements, but you could use these for usual attributes as cellspacing, cellpadding, align, etc...

<?php
$renderer 
=& $datagrid->getRenderer();

/* For the <table> element : */
$renderer->setTableAttribute("class""fruits");

/* For every odd <tr> elements */
$renderer->setTableOddRowAttributes(array ("class" => "odd"));

$pagingHtml $renderer->getPaging();
echo 
"<p class=\"paging\">Pages : $pagingHtml</p>";
$datagrid->render();
?>

Now let's add some CSS :

Hope you like it...

VI. Conclusion and links

The aim of this tutorial is to teach you the fundamentals of the bind() method, how and why you can tightly integrate a datagrid and a dataobject. It can drastically reduce the need to code what you've been coding again and again.

For any real-life project, you shouldn't stop here though :

VII. The Author

My name is Olivier Guilyardi, I am located in Paris, France, and offer software development services under the commercial name of Samalyse.

I'm the original author of the bind() method and of the DataSource layer, that are part of the official Structures_DataGrid distribution. Since it has become quite popular I decided to write this tutorial, to let even more people benefit from my idea.

Icon
Faites-nous part de votre projet
Progress bar