PopSQL

What is it?

PopSQL (pronounced "popsicle") provides a simple, objective approach to conditionally constructing MySQL statements.

Why do I want it?

Building conditional queries by hand is tedious, error prone, and ugly:

$fields = ['field1', 'field2'];
if ($someCondition)
   $fields[] = 'field3';
else
   $fields[] = 'field4';
$fieldClause = implode(',', $fields);

$whereConditions = [];
$whereParameters = [];
if ($someCondition) {
   $whereConditions[] = 'field1 > ? AND field3 < ?';
   $whereParameters[] = 0;
   $whereParameters[] = 1337;
} else if ($someOtherCondition) {
   $whereConditions[] = 'field4 != "" OR field2 < ?';
   $whereParameters[] = 0;
}

if ($whereConditions)
   $whereClause = 'WHERE (' . implode('', ) . ')';
else
   $whereClause = '';

$query = <<<EOT
SELECT $fieldClause
FROM my_table mt
JOIN my_other_table mot ON mt.field1 > mot.value + ?
$whereClause
EOT;

$params = array_merge([7], $whereParameters);

How do I use it?

Working with an object is much prettier, and much harder to mess up:

$qGen = new QueryGenerator();

$qGen->select(['field1', 'field2']);
$qGen->select($someCondition ? 'field3' : 'field4');

$qGen->from('my_table mt')->
 join('JOIN my_other_table mot ON mt.field1 > mot.value + ?', 7);

if ($someCondition) {
   $qGen->where('field1 > ? AND field3 < ?', [0, 1337]);
} else if ($someOtherCondition) {
   $qGen->where('field4 != "" OR field2 < ?', 0);
}

list($query, $params) = $qGen->build();

Assuming $someCondition is true and $someOtherCondition is false, the above example produces the following query:

SELECT field1, field2, field3
FROM my_table mt
JOIN my_other_table mot ON mt.field1 > mot.value + ?
WHERE field1 > ? AND field3 < ?

... and the following parameters:

[7, 0, 1337]

QueryGenerator has support for SELECT, INSERT, REPLACE, UPDATE, and DELETE queries. Each of those query types supports a selection of different clauses:

• select: from, join, where, group, having, union, order, limit, offset, forupdate
• insert: set, columns, values, duplicate
• replace: set, columns, values
• update: set, where, order, limit
• delete: from, where, order, limit

Simply call the member function of the clause you want to add to, passing strings of SQL and (optionally) parameters for use in prepared statements.

SELECT queries can be combined with union() and unionAll(). Operands must be complete SELECT statements, either another QueryGenerator or a raw SQL string:

$archived = new QueryGenerator();
$archived->select('id')->from('archived_orders')->where('total > ?', 100);

$qGen = new QueryGenerator();
$qGen->select('id')->from('orders')->where('total > ?', 100);
$qGen->union($archived);
$qGen->unionAll('SELECT id FROM pending_orders');
$qGen->order('id');

A trailing order() or limit() applies to the whole union result. To order or limit an individual operand, pass it as a parenthesized string.

QueryGenerator operands are rendered when union() or unionAll() is called; mutating the operand afterward has no effect on the outer query.