![[Skip the navigation bar]](../../graphics/dot.gif)
The SQL Solution is a set of open source PHP classes to simplify integrating databases with web pages. Provides a powerful, user friendly, platform independent API (Application Programming Interface) for MySQL (via PHP's mysql or mysqli extensions), PostgreSQL, SQLite (via PHP's sqlite or sqlite3 extensions), and ODBC database management systems. Output is XHTML compliant and handicapped accessible.
Here's some of its key features:
When you use the SQL Solution to execute a query, it automatically connects to the database server. You don't need to spend time coding that yourself.
In addition to a standard query execution function, we've included functions which both run queries and return results. One example is the RunQuery_NoDuplicates() function, which comes in handy when you need to update information that can't violate unique indexes.
The program is bullet proof. All errors are trapped, a generic message is printed to the screen and program execution halts. Such behavior keeps the public from determining specifics of your system's construction, preventing discovery of means to exploit it.
System administrators can turn on SQL Solution's debugging tools, which pinpoint which of your scripts caused the problem and which line to look at as well as displaying the most recent query string and a description of the problem.
Browsers and scripts can be tripped up by certain characters. Even worse, Java Scripts that do nasty things can be embedded into your HTML (CERT Advisory CA-2000-02).
To safely handle user input heading toward your programs, consider using our Form Solution, which automatically cleans user input. It also has several handy input validation and form element generation functions.
Even if user input is strictly screened via your scripts, there's always the potential for subversion from within your organization or ISP. So, SQL Solution offers the option to screen all output, converting HTML special characters (< > & ") into their Character References (< > & ").
Using the output screening unfortunately eliminates the ability to retrieve HTML content from your databases. So, to establish a secure HTML enabled environment, we've created a Safe Markup Language.
The basis of our Safe Markup Language is the "::" delimiter. Anything contained between two pairs of colons, say ::p:: for example, is evaluated as a potential HTML language construct. If the construct is allowed, it's converted from Safe Markup Language into HTML.
In addition, the Safe Markup Language conversion process automatically turns all URI's into hyperlinks. So, http://www.analysisandsolutions.com/ would become http://www.analysisandsolutions.com/.
The class contains several functions for displaying individual records and entire record sets returned by queries. Layouts include tables and unordered bullet lists. Each record can have a hyperlink automatically inserted, allowing users to jump to another page set up to display further details on that record. The border, width, line wrapping and cell padding are also customizable. For example, this lovely table
Reported Room Description Reporter 2000-01-01 09 Water leaking to downstairs Daniel Convissor 2000-03-19 06 TV antenna on roof broken Ali Farka Toure 2000-04-09 07 Bathtub drain is clogged Annie Lennox 2000-04-20 Roof Aliens removed door Ani DiFranco
was rendered in three steps:
|
Another nifty result formatting tool is the RecordSetAsTransform() function. My Census data is laid out in an efficiently "normalized" table format, something like this:
|
What's good for a database isn't good for reading by people, especially when you get into larger data sets. Here's a nicer configuration of the same data:
Can you say "Wow, that's easier to read!" I knew you could. :-) Some database programs can do this using a TRANSFORM command in the SQL statement. Microsoft Access does it via "Crosstab Queries." Unfortunately, MySQL doesn't handle them, and that's the database server we work with. |
So, SQL Solution gives the option to reconfigure any "normalized" data structure into a crosstabulated spreadsheet-like HTML table. This code is beautiful and flexible, allowing manipulation of any query result. It even transposes the rows and columns when necessary to lay out the larger axis along the vertical portion of the page.
For the two table display functions shown above, you can also specify a special query string to dynamically display credits for the data displayed in the table itself.
Other procedures include tools to dynamically produce option lists, check boxes and radio buttons for HTML forms, based on data actually in the database. Why, typing in this simple command...
$Opt['table'] = 'States';
$Opt['keyfield'] = 'SC';
$Opt['visiblefield'] = 'SN';
$Opt['name'] = 'Codes';
$Opt['where'] = "SC between '32' and '36'";
$Opt['orderby'] = 'SN';
$Opt['default'] = array('34', '36');
$SQL->InputListGenerator(__FILE__,__LINE__, $Opt);
...whips up the following tidy set of input boxes:
You even get to select how many columns are used to display the data.
The RecordSetAsInput() function is another handy tool, with which you can turn the results of a query into form elements with hyperlinks to descriptions of each item:
Output from the SQL Solution is XML compliant. XML is the emerging standard for web content, making our results ready for a new generation of mobile computing devices. The XML standard adhered to is the W3C's "Strict" Extensible HyperText Markup Language (XHTML) 1.0 DTD. In addition, there's a function that dumps out the query results as straight XML.
When HTML tables are used to display results, they are constructed for ease of use by disabled persons via the W3C's HTML Techniques for Web Content Accessibility Guidelines 1.0.
SQL Solution is written with portability in mind. It's an API (Application Programming Interface) which your scripts call rather than the databases themselves. This permits switching database types without having to rewrite your applications. While the SQL Solution is presently written for MySQL, PostgreSQL, SQLite and ODBC databases, the vast majority of the program is database independent. Reworking the class to handle another database requires only minor modifications to 15 functions containing a total of roughly 20 database specific commands. Though do note, Query String syntaxes vary from database to database, so switching databases may require tweaking the queries in your pages.
In addition, the program is segmented into separate classes in order to make upgrades easy. There's even a separate class to stick your own customized functions into.
If you find the whole system to be overkill, there's several handy elements in there. You can strip down the class to keep the parts you want. Similarly, you can extract such elements and use them directly in your code.
We've started adding documentation in the code itself, using phpDocumentor docblock comments. This process isn't complete, but should be in the near future.
SQL Solution is a trademark of The Analysis and Solutions Company.