Simple Data Grid Using PHP and jQuery

Introduction

This article walks though the process of writing a simple Web-based data grid application for browsing a database table, using the PHP Web scripting language, jQuery Javascript library and MySQL DBMS.

Requirement and Design

The grid is just an HTML table, with filters for each column and pagination controls at the bottom of the table. The filters are implemented using drop down lists, and the pagination can be done using two buttons, one to move to the next page, the other to move to the previous page of data:

+-------------+-------------+
|Column header|Column header|
| Filter      | Filter      |
+-------------+-------------+
|Data row1                  |
|Data row2                  |
|Data row3                  |
+-------------+-------------+
|Pagination controls        |
+-------------+-------------+

Two other requirements are to ensure that the data grid can be used with or without Javascript, and that it can be used in Firefox, MSIE and Opera.

One We Prepared Earlier …

To orient you, check out the data grid implementation first. Try it with Javascript enabled and disabled on your browser. When Javascript is disabled, you have to first select a filter value, then press the Submit button before the grid is updated.

When Javascript is enabled, just changing a filter value will update the grid and you can reset all the filters at once by pressing the Reset button. Also, the presentation is slightly enhanced by zebra-striping the rows to make them easier to view.

As you filter or paginate the data, the SQL query, below the grid, is updated.

Test Data

To allow us to develop and test the grid, we create some test data in the MySQL database. I use the same schema and and data from an earlier posting. Below is the SQL statement to create the table:

create table if not exists p0020_shirt (Region varchar(8), Category varchar(8), Shirt_Style varchar(8), ShipDate date, Units integer, Price decimal(4,2), Cost decimal(4,2));

We add data into the table using a series of insert statements, like the following:

insert into p0020_shirt values ('East','Boys','Tee',date('2005-01-01'),11,5.25,4.66);
insert into p0020_shirt values ('East','Boys','Golf',date('2005-01-01'),12,5.26,4.57);
insert into p0020_shirt values ('East','Boys','Polo',date('2005-01-01'),13,5.27,5.01);
insert into p0020_shirt values ('East','Girls','Tee',date('2005-01-01'),14,5.28,5.01);
…

We filter and paginate our data in one select statement using the where, and limit and offset clauses:

select * from [table reference] [filter] limit [number of rows] offset [position]

The filter is just a where clause, generated when the user selects a column and value to filter the rows.

The filter controls are implemented using drop down lists, and to populate them, we fetch all unique values for each string column using the distinct option in a select statement, such as: select distinct [column name] from [table reference] order by 1 asc.

When populating the drop down lists, we should also let the user reset the filter. To this end, we add a dummy value 'All' into the drop down list. Rather than having a special step in the PHP code when it generates the drop down lists, we can use union to combine the results of two select statements into one result set, then use a loop to populate the drop down list:

select 'All'
union
select distinct [column name] from [table reference] order by 1 asc

For example, the statement above would generate a list like (All, East, North, South, West).

The pagination controls just modify the offset position by adding or subtracting a constant and the current offset. We can easily stop the user from paging before the first row by ensuring that the offset value is always 0 or more. To stop the user from paging beyond the last row, we use another query to count the number of rows a query would return: select count(*) from [table reference] [filter]. (In the PHP code, we also pad the table with empty rows if there are fewer rows in the dataset than the standard number of rows so that the height of the table doesn't change in the last page.)

PHP Data Grid Implementation

By moving as much of the logic into SQL statements, the PHP implementation of the data grid control is straightforward. If you view the PHP source code, you will see that the entry point, the main() function, initializes variables using default values or from a previous form submission, connects to the database, then paints the drop down lists, data rows and pagination controls.

Enhance Data Grid UI with Javascript and jQuery

HTML forms should be viewable and usable without Javascript. If Javascript is enabled, then we can enhance the presentation and usability. With this in mind, the PHP code should just generate the non-Javascript form and create no event handlers, and if Javascript is enabled, the browser should use Javascript to add event handlers to HTML elements.

One library that makes it easier to manipulate the DOM in a browser is jQuery. For instance, the laborious DOM function calls such as document.getElementsById() are replaced by simpler ones such as $().

In this data grid example, the Javascript functions to enhance the presentation or add interactivity can be found page's head element.

Perhaps one odd feature is to hide the Submit button if Javascript is enabled. If Javascript is disabled, then the user must press the Submit button to submit the form. On the other hand, if Javascript is enabled, just changing the drop down list causes the onchange event handler to submit the form, enabled, so the Submit button is redundant.

Unexpected Problems

While developing this data grid control, I stumbled upon two unexpected problems.

The first problem is that the form can't reset select elements (the filters) to the first option in the list. Resetting the select elements just sets them to the default option, which is the option chosen when this form is generated in PHP.

The second problem was the Internet Explorer Submit Button Bug.

Improvements

One obvious improvement to this data grid is to reduce the number of database queries required just to update the data rows or filters. At the moment, there are 5 queries (one for each filter, one for the data rows, and one for counting the number of rows) each time the page is updated. While this is not a problem for a small dataset or a small number of users, it may quickly overload a server with a lot of users and more complex queries. This improvement can be implemented using AJAX to update each control without refreshing the entire page.

Another improvement is to auto-generate the filter lists based on rules of the number of unique values and the column types. For example, a filter for a text column could allow the user to enter a regular expression or to auto-complete as the user enters more letters. A filter for numeric values could automatically generate quartiles. Finally, a filter for dates could automatically contain months, quarters or years.

Conclusion

This article has presented a way to implement a simple data grid control using PHP and jQuery. SQL queries are used as much as possible to simplify the page generation logic in PHP. PHP is used to provide the business logic to query the database and generate a basic form. jQuery (and Javascript) are used to enhance the usability and presentation of the page. I'll keep exploring this approach to see if it would make it easier to develop and maintain.