While redeploying this site I came across the need to have a simple reporting tool to display SQL queries. Previously I had a SQL utility open on my desktop and would connect and run queries as needed. But I need to be on my home network or open open up the firewall on the server. Both solutions are not ideal so back to the drawing board. Instead it is easy enough to create a webpage using PHP and exposure the queries I use the most.
Luckily I had created such a tool long ago when I had worked on my Twitter scraper in PHP. At the time I had the exact same requirements for viewing data quickly and easily. So I was able to put this together pretty quick. I figured since the tool has multiple uses on any such platform it would be more appropriate to maintain it as a stand alone package.
The tool is essentially a single PHP page backed by a MySQL data table publishes the results of SQL views. Essentially each row in the provided SQL table represents a report. The details of the table contain the name, display name, url, sort order and active flag. The name is used by the PHP page to lookup the report definition in the SQL table. The display name is what the PHP prints in the list of available reports. The URL is what is drawn in the HTML code of the PHP page. This should be a reference to the PHP page with the report name parameter appended, even though it could link to any page desired. The sort order determined the order in which available reports are displayed and the active flag determines if the given report is displayed in the list of reports.
The PHP page will hit the MySQL server to determine if the view exists when a report is called,. The SQL object defined must be a view. If it does exist it will pull the column names from the information schema and build a table header with those names. It will then select all rows from the view and draw the HTML table.
The report pulls the entire view contents so it’s best to limit the number rows returned for performance reasons. There is a CSS file which defines some basic attributes such as font type and size plus background color.
Not much more to it than that. You can find this on my Github page.