PDatagrid. Paginate query results in a grid-based layout

There are times when our web based applications need to display a large amount of tabular data. Forcing users to scroll a single huge page is an inefficient and user unfriendly solution. Creating a multi-page display with hyperlinks that allow users to navigate through different pages of data is a much better approach.

PDatagrid is a php class to paginate data coming from a database query and display them in a grid format with page navigation links. By writing sufficiently generic code we will be able to utilize this class in many different situations where pagination of data is necessary.

The package (pdatagrid.zip) contains the following files

• pdatagrid.class.php class definition file
• docs/ Complete documentation generated by PHP Documentor
• example1.php A simple example to demostrate how to use the class
• example2.php Another example
• style.css A css file to customize datagrid appearance
• dbconfig.php database constants (used by examples)
• composers.sql Sql script to re-create the same table used in examples

How to use the class

Even if using the class is not difficult here are all the steps you need to follow to use PDatagrid in your scripts. All the code you see below comes from Example 1 (example1.php).

1) Include the class definition file

require 'pdatagrid.class.php';

This is the first obvious step.

2) Establish a database connection

require 'dbconfig.php';
$conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) 
  or die('Connection to database failed: ' . mysql_error());
mysql_select_db(DB_NAME) or die ('select_db failed!');

You need to modify dbconfig.php with host, database name, user name and password needed to access your own database.

3) Create an instance of the class

$grid = new PDatagrid($conn);

We pass the connection handle to PDatagrid class constructor to initialize the new object with the previously established database connection.

4) Set the SQL queries to count / select records

$grid->setSqlCount("Select count(*) from composers");
$grid->setSqlSelect("Select name, date_format(date_birth,'%b %D %Y') db, 
  date_format(date_death,'%b %D %Y') dd from composers");

Two distinct SQL queries need to be set. They are respectively used to compute the total number of rows (records) and select the columns (fields) that will be displayed in the grid.

A WHERE clause is not included because in this example we want to display the whole table. When needed the same WHERE clause must be added to both queries or pagination of data will not work correctly.

5) Set base url for navigation links

$grid->baselink = 'example1.php';

This is used to create links to single pages of data.

6) Set maximum number of page navigation links.

$grid->setMaxNavLinks(4);

These are the numbered links usually displayed at the bottom of the grid.

7) Set the number of rows (records) per page.

$grid->setRowsPerPage(5);

8) Set current page number

if(isset($_GET['page']))
	$grid->setPage($_GET['page']);

If $_GET['page'] is not set then we are on the first page of results, otherwise a navigation link has been clicked and url parameter page contains the index of the page to display.

9) Create table header

<table cellspacing="0" cellpadding="0" width="500">
<thead>
<tr>
  <td width="50%">Name</td>
  <td width="25%">Date of Birth</td>
  <td width="25%">Date of Death</td>
</tr>
</thead>

10) Generate navigation links and include them within table footer

<tfoot>
<tr>
  <td colspan="3">
  <span id="navlinks">
    <?php echo $grid->getLinks();?>
  </span>
  </td>
</tr>
</tfoot>

11) Generate rows for table body

<tbody>
  <?php echo $grid->getRows();?>
</tbody>
</table>

Customizing grid style

Grid appearance is almost entirely controlled with css (style.css). Among the other things, by customizing the stylesheet you can choose a different background color for odd and even rows of data, change the font family, style and size of column headings, change the appearance of navigation links.

Other examples

Example 2 (example2.php) demonstrates how to display records using unordered lists in place of a grid . Check the code to see how easily you can configure PDatagrid to render paginated data in a customized layout.

The need to paginate query results frequently occurs while developing web based applications. With the use of a php class we can avoid to write the same code over and over.

If you find this class useful, discover a bug or want to suggest some improvements please let us know.