Blog

PHP. Tiniest SQL Query Builder

Look what I found. This is the tiniest SQL query builder implemented as single function. Original: http://gabordemooij.com/index.php?p=/tiniest_query_builder

function build_query($pieces) {
  $sql = '';
  $glue = NULL;
  foreach( $pieces as $piece ) {
    $n = count( $piece );  
    switch( $n ) {
      case 1:
        $sql .= " {$piece[0]} ";
        break;
      case 2:
        $glue = NULL;
        if (!is_null($piece[0])) $sql .= " {$piece[1]} ";
        break;
      case 3:
        $glue = ( is_null( $glue ) ) ? $piece[1] : $glue;
	if (!is_null($piece[0])) { 
		$sql .= " {$glue} {$piece[2]} ";
		$glue = NULL;
	}
        break;
    }
  }
  return $sql;
}

The function pads spaces automatically. Yet, its a pure SQL, no new syntax!

And here is a little example how to use it:

$sql = build_query([
    ['SELECT * FROM book'],
    [$title, 'WHERE', 'title = ?'],
    [$price, 'AND', 'price < ?'],
    [$order, 'ORDER BY ? ASC'],
    [$limit, 'LIMIT ?']
]);

 

Rules are simple:

1. The array must be in logical sequence

2. The array elements are sequentially concatenated to the end of the SQL string

3. Each arrat can have one, two or three elements.

- If it has one element, the element is simply concatenated to the final query.
- If it has two elements, the second element will be concatenated only if the first evaluates to TRUE.

- If is has three elements works the same as a piece with two elements, except that it will use the glue provided in the second element to concat the value of the third element. The glue acts as a little tube of glue. If there is still glue left in the tube (WHERE) it will preserve this until it can be applied (so the first AND will be ignored in case of a WHERE condition).