SQL Query Builder :: The Idea

Today I decided to create an SQL query builder class for PHP and MySQL. I am envisioning a single class interface with basic, easy to understand methods. The class should handle the vast majority of MySQL data manipulation statements including all JOIN configurations and nested queries. The class needs to provide a basic SQL statement validation service via Exception throws. This could become a big project.

Within my own PHP framework, I use an SQL generator that was written by myself. This set of classes works great and handles JOINS, nested queries, complex ON clauses, table aliases, and formatable SELECT clauses. The problem, however, and the need for a rewrite, is the user interface. My current “sqlStatement” class requires the user to explicitly create the table objects, table field objects and even join objects each with assigned criteria, or condition, objects. This creates a need for up to ten lines of code to generate even the simplest of queries. Some code that requires the joining of numerous tables can reach a hundred or so lines. The new design will still utilize many statement type specific classes, but the creation of these classes should be done within the main class. A user should just be able to add a WHERE clause without needing to create each object responsible for making up that clause.

The backbone of much of an SQL statement is comprised of name => value pairs. A WHERE clause contains x (name) [operator] y (value). Both the “name” and “value” portions can be simple string or numeric values, column references, full SQL subqueries, formatted date values, or even a list of values as used within IN, ANY, ALL, SOME statements. This name/value construct could possibly be used for SET clauses as with UPDATE statements, both sides of the VALUES syntax with INSERT statements, or even the conditions by which a JOIN is calculated. I still need to determine if it can be expanded to include the table references within a FROM clause. Also maybe the “xyz AS x” pairs used for SELECT statements. Of course we would need to retain that AS alias for use within HAVING/GROUP BY clauses.

Initially, I will be ignoring any database/table manipulation functions. No need to ALTER, CREATE, DROP, RENAME or TRUNCATE functions. Too dangerous to provide an easy way to screw things up. Transaction, procedure and locking types functions will also be left out at first. Let us see if I can get this to work first. If it is done properly, expansion should be as easy as adding a module or new class. I hope to include everything you would expect from a basic builder with the addition of all JOIN statements, UNION, USING, and any subquery structure like IN, ANY, EXISTS, etc. Also to allow the ability of a subquery within the WHERE, FROM, SELECT and JOIN clauses.

Step One: Figure out how I am going to do this.