Flexible Data Display with PDO and HTML_Table_Matrix
Have you ever wanted to set up an application that displays tabular data, and wanted to use the same view for each query? While PHP is excellent for writing small-footprint queries, its ability to be embedded in HTML leaves the creativity to the programmer to figure out a consistent display solution for tabular data.
We need two things – first, we need a way to dynamically retrieve table information, query information, and query results. Second, we need a way to dynamically build the display based on the information we have. Using the metadata methods of PDO, along with PEAR::HTML_Table_Matrix, we can do just this.
Now I will be the first to say that many frameworks, ORM’s, CMS’s, and the like handle this task aptly. But with these applications come bloat that may be unnecessary for your needs. Our application requires the following:
- PDO – Comes standard with PHP > 5.1, available through PECL for 5.0.
- PEAR classes HTML_Table, and HTML_Table_Matrix. Install using the typical PEAR installation methods.
The script is hosted on the “Useful Scripts” page, so I will not write it out here, but instead highlight details.
- The method PDOStatement::getColumnMeta(int $column) is used to retrieve table headers. This can be achieved by using a zero-based index in a simple for loop, the limit of that loop being retrieved using PDOStatement::columnCount().
- PDOStatement::columnCount() and PDOStatement::rowCount() are used to limit the dimensions for HTML_Table_Matrix rendering.
- Important! – HTML_Table_Matrix will take a 1-dimensional array, so use array_merge() to “flatten” your query results.
- HTML_Table_Matrix takes parameters for attributes to be given to the table. This can include id, classes, etc. for additional CSS styling or JS interactivity.
The following screenshot shows the results of the following query:
SELECT id, name, email, phone FROM contacts ORDER BY id ASC
Query Results - Basic Display
Let me know how the script works for you! And good luck.