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 | +-------------+-------------+
One We Prepared Earlier …
As you filter or paginate the data, the SQL query, below the grid, is updated.
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
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 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.
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
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.
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.