Building an OOP SQL query builder

PHP 7 implements something called a context sensitive lexer. What this means is that words that are normally reserved can be used if the context allows. Thus, when building an object-oriented SQL builder, we can get away with using methods named and, or, not, and so on.

How to do it...

We define a Application\Database\Finder class. In the class, we define methods that match our favorite SQL operations:

namespace Application\Database;
class Finder
{
  public static $sql      = '';
  public static $instance = NULL;
  public static $prefix   = '';
  public static $where    = array();
  public static $control  = ['', ''];
  
    // $a == name of table
    // $cols = column names
    public static function select($a, $cols = NULL)
    {
      self::$instance  = new Finder();
      if ($cols) {
           self::$prefix = 'SELECT ' . $cols . ' FROM ' . $a;
      } else {
        self::$prefix = 'SELECT * FROM ' . $a;
      }
      return self::$instance;
    }
    
    public static function where($a = NULL)
    {
        self::$where[0] = ' WHERE ' . $a;
        return self::$instance;
    }
    
    public static function like($a, $b)
    {
        self::$where[] = trim($a . ' LIKE ' . $b);
        return self::$instance;
    }
    
    public static function and($a = NULL)
    {
        self::$where[] = trim('AND ' . $a);
        return self::$instance;
    }
    
    public static function or($a = NULL)
    {
        self::$where[] = trim('OR ' . $a);
        return self::$instance;
    }
    
    public static function in(array $a)
    {
        self::$where[] = 'IN ( ' . implode(',', $a) . ' )';
        return self::$instance;
    }
    
    public static function not($a = NULL)
    {
        self::$where[] = trim('NOT ' . $a);
        return self::$instance;
    }
    
    public static function limit($limit)
    {
        self::$control[0] = 'LIMIT ' . $limit;
        return self::$instance;
    }
    
    public static function offset($offset)
    {
        self::$control[1] = 'OFFSET ' . $offset;
        return self::$instance;
    }

  public static function getSql()
  {
    self::$sql = self::$prefix
       . implode(' ', self::$where)
               . ' '
               . self::$control[0]
               . ' '
               . self::$control[1];
    preg_replace('/  /', ' ', self::$sql);
    return trim(self::$sql);
  }
}

Each function used to generate an SQL fragment returns the same property, $instance. This allows us to represent the code using a fluent interface, such as this:
```
$sql = Finder::select('project')->where('priority > 9') ... etc.
```

How it works...

Copy the code defined precedingly into a Finder.php file in the Application\Database folder. You can then create a chap_05_oop_query_builder.php calling program, which initializes the autoloader defined in Chapter 1, Building a Foundation. You can then run Finder::select() to generate an object from which the SQL string can be rendered:

<?php
require __DIR__ . '/../Application/Autoload/Loader.php';
Application\Autoload\Loader::init(__DIR__ . '/..');
use Application\Database\Finder;

$sql = Finder::select('project')
  ->where()
  ->like('name', '%secret%')
  ->and('priority > 9')
  ->or('code')->in(['4', '5', '7'])
  ->and()->not('created_at')
  ->limit(10)
  ->offset(20);

echo Finder::getSql();

Here is the result of the precding code:

Table of Contents for
PHP 7: Real World Application Development

Building an OOP SQL query builder

How to do it...

How it works...

See also

Table of Contents for PHP 7: Real World Application Development

Building an OOP SQL query builder

How to do it...

How it works...

See also

Table of Contents for
PHP 7: Real World Application Development