Query Builder Php

README.md

Query builder

Select

$queryBuilder = QueryBuilder::create($database->connection)
  ->select()
  ->from('users')
  ->where('age', '>', 18)
  ->orWhere('fake_id, '=', TRUE)
  ->innerJoin('dads', 'dads.disapointment_id', 'users.id')
  ->limit(10)
  ->offset(0)

if (isset($_GET['search'])) {
  $queryBuilder->where('fname', 'LIKE', '%' . $_GET['search'] . '%');
}

$users = $queryBuilder->execute() // Array of users

$queryBuilder = QueryBuilder::create($database->connection)
  ->select()
  ->from('users')
  ->where('age', '>', 18)
  ->first()

if (isset($_GET['search'])) {
  $queryBuilder->where('fname', 'LIKE', '%' . $_GET['search'] . '%');
}

$user = $queryBuilder->execute() // One user of NULL

Insert

QueryBuilder::create($database->connection)
    ->insert()
    ->into('users')
    ->values([
      'email' => $_POST['email'],
      'fname' => $_POST['fname'],
      'pass' => password_hash($_POST['pass'], PASSWORD_BCRYPT),
      'lname' => $_POST['lname'],
      'gender' => $_POST['gender'],
    ])
    ->execute(); // Returns the statement already closed

Update

QueryBuilder::create($database->connection)
    ->update()
    ->table('users')
    ->set([
      'fname' => 'updated'
    ])
    ->where('id', '=', 1)
    ->execute(); // Returns the statement already closed

By default, the compiler refuses to allow updates without where clauses:

QueryBuilder::create($database->connection)
    ->update()
    ->table('users')
    ->set([
      'fname' => 'updated'
    ])
    ->execute(); // Exception: 'Dangerous query, update without where...`

To explicit allow dangerous queries, you can chain the allowDangerousQueries() method:

QueryBuilder::create($database->connection)
    ->update()
    ->table('users')
    ->set([
      'fname' => 'updated'
    ])
    ->allowDangerousQueries()
    ->execute(); // Success

Delete

QueryBuilder::create($database->connection)
  ->delete()
  ->from('users')
  ->where('age', '>', 18)
  ->first()

The same way as the update, the compiler refuses to allow deletes without where clauses:

QueryBuilder::create($database->connection)
  ->delete()
  ->from('users')
  ->execute(); // Exception: 'Dangerous query, delete without where...`

To explicit allow dangerous queries, you can chain the allowDangerousQueries() method:

QueryBuilder::create($database->connection)
  ->delete()
  ->from('users')
  ->allowDangerousQueries()
  ->execute(); // Success

Raw

$result = QueryBuilder::create($database->connection)
  ->raw('select * from users where email = ?')
  ->setBindings(['[email protected]'])
  ->execute();

QueryBuilder.php

<?php
define('QUERY_BUILDER_SEE_DEBUG', false);
define('QUERY_BUILDER_SORT_ASC', 'ASC');
define('QUERY_BUILDER_SORT_DESC', 'DESC');

define('QUERY_BUILDER_JOIN_INNER', 'INNER JOIN');
define('QUERY_BUILDER_JOIN_LEFT', 'LEFT JOIN');
define('QUERY_BUILDER_JOIN_RIGHT', 'RIGHT JOIN');
define('QUERY_BUILDER_JOIN_FULL', 'FULL JOIN');

const ALLOWED_OPERATORS = ['=', '!=', '>', '<', '>=', '<=', 'ILIKE', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN'];


class QueryBuilder
{
  /**
   * The connection to the database.
   */
  public mysqli $connection;
  /**
   * The instance of the SelectQueryBuilder or InsertQueryBuilder class.
   */
  private SelectQueryBuilder|InsertQueryBuilder|UpdateQueryBuilder|DeleteQueryBuilder $queryBuilder;

  function __construct(mysqli $connection)
  {
    $this->connection = $connection;
  }

  /**
   * Select columns from the table.
   * This function should not recieve user input directly, as it is vulnerable to SQL injection.
   */
  function select(?array $columns = ['*']): SelectQueryBuilder
  {
    if (isset($columns) && !is_array($columns)) {
      $columns = [$columns];
    }

    $this->queryBuilder = new SelectQueryBuilder($this, $columns ?? ['*']);
    return $this->queryBuilder;
  }

  /**
   * Insert data into the table.
   */
  function insert(): InsertQueryBuilder
  {
    $this->queryBuilder = new InsertQueryBuilder($this);
    return $this->queryBuilder;
  }

  /**
   * Update data in the table.
   */
  function update(): UpdateQueryBuilder
  {
    $this->queryBuilder = new UpdateQueryBuilder($this);
    return $this->queryBuilder;
  }

  /**
   * Delete data from the table.
   */
  function delete(): DeleteQueryBuilder
  {
    $this->queryBuilder = new DeleteQueryBuilder($this);
    return $this->queryBuilder;
  }

  /**
   * Sanitize the name of a column or table.
   */
  function sanitizeName(string $name): string
  {
    return $this->connection->real_escape_string($name);
  }

  /**
   * Convert value to the bind parameter type.
   */
  function getBindValueType(mixed $value): string
  {
    // Return the bind parameter type based on the type of the value.
    // Available types are: i - integer, d - double, s - string, b - BLOB (not implemented).
    switch (gettype($value)) {
      case 'integer':
        return 'i';
      case 'double':
      case 'float':
        return 'd';
      case 'string':
        return "s";
      default:
        return 's';
    }
  }

  function execute()
  {
    if (!isset($this->queryBuilder)) {
      throw new Exception('No query type specified');
    }

    return $this->queryBuilder->execute();
  }

  /**
   * Create a new instance of the QueryBuilder class.
   */
  public static function create(mysqli $connection)
  {
    return new QueryBuilder($connection);
  }
}

class InsertQueryBuilder
{
  /**
   * The instance of the QueryBuilder class that created this instance.
   */
  private QueryBuilder $queryBuilder;
  /**
   * The table to insert into.
   */
  private string $table;
  /**
   * The columns to insert into.
   */
  private array $columns = [];
  /**
   * The values to insert.
   */
  private array $values = [];

  function __construct(QueryBuilder $queryBuilder)
  {
    $this->queryBuilder = $queryBuilder;
  }

  /**
   * Set the table to insert into.
   */
  function into($table)
  {
    $this->table = $table;
    return $this;
  }

  /**
   * Set the data to insert.
   */
  function values(array $data): InsertQueryBuilder
  {
    // Sanitize the column names and values and store them in the columns and values arrays.
    foreach ($data as $column => $value) {
      $this->columns[] = $this->queryBuilder->sanitizeName($column);
      $this->values[] = $value;
    }

    return $this;
  }

  /**
   * Build the query.
   */
  function buildQuery(): mysqli_stmt
  {
    // Convert the columns and values arrays into strings to use in the query, e.g. (column1, column2, column3) and (?, ?, ?).
    $columns = implode(', ', $this->columns);
    // Create a string of question marks to use as placeholders for the values, e.g. ?, ?, ?.
    $values = implode(', ', array_fill(0, count($this->values), '?'));

    // Create the query string, e.g. INSERT INTO table (column1, column2, column3) VALUES (?, ?, ?).
    $query = "INSERT INTO $this->table ($columns) VALUES ($values)";

    if (QUERY_BUILDER_SEE_DEBUG) print_r($query . "\n");

    // Prepare the query
    $statement = $this->queryBuilder->connection->prepare($query);

    // Throw an exception if the query is invalid.
    if (!$statement) {
      throw new Exception($this->queryBuilder->connection->error);
    }

    // Create a string of types to use in the bind_param function, e.g. 'sss'.
    $types = '';
    foreach ($this->values as $value) {
      $types .= $this->queryBuilder->getBindValueType($value);
    }

    // Bind the values to the statement to prevent SQL injection.
    $statement->bind_param($types, ...$this->values);

    if (QUERY_BUILDER_SEE_DEBUG) {
      print_r($this->values);
      echo "\n";
    }

    return $statement;
  }

  function execute()
  {
    // Execute the query and close the statement.
    $statement = $this->buildQuery();
    $statement->execute();
    $statement->close();
  }
}

class SelectQueryBuilder
{
  /**
   * The instance of the QueryBuilder class that created this instance.
   */
  private QueryBuilder $queryBuilder;
  /**
   * The table to select from.
   */
  private string $table;
  /**
   * The columns to select.
   */
  private array $columns;
  /**
   * The joins to perform.
   */
  private array $joins = [];
  /**
   * The where clauses.
   */
  private array $wheres = [];
  /**
   * The order by clauses.
   */
  private array $orders = [];
  /**
   * The limit of rows to return.
   */
  private int $limit;
  /**
   * The offset of rows to return.
   */
  private int $offset;
  /**
   * Return first row only.
   */
  private bool $first = false;

  /**
   * The constructor of the SelectQueryBuilder class.
   */
  function __construct(QueryBuilder $queryBuilder, array $columns)
  {
    $this->queryBuilder = $queryBuilder;
    // Sanitize the column names and store them in the columns array.
    $this->columns = array_map(fn ($column) => $this->queryBuilder->sanitizeName($column), $columns);
  }

  /**
   * Set the table to select from.
   */
  function from($table)
  {
    $this->table = $table;
    return $this;
  }

  /**
   * This function should not recieve user input directly, as it is vulnerable to SQL injection.
   *
   * Perform a join on the table.
   */
  function join($table, $column1, $column2, $type = 'JOIN'): SelectQueryBuilder
  {
    // sanitize the table and column names and store them in the joins array.
    $sanitizedTable = $this->queryBuilder->sanitizeName($table);
    $sanitizedColumn1 = $this->queryBuilder->sanitizeName($column1);
    $sanitizedColumn2 = $this->queryBuilder->sanitizeName($column2);
    // Add the join to the joins array.
    $this->joins[] = [
      'type' => $type,
      'table' => $sanitizedTable,
      'column1' => $sanitizedColumn1,
      'column2' => $sanitizedColumn2
    ];
    return $this;
  }

  /**
   * Add a where clause to the query.
   * Allowed operators: =, !=, >, <, >=, <=, ILIKE, LIKE, NOT LIKE, IN, NOT IN
   */
  function where($column, $operator, $value): SelectQueryBuilder
  {
    // Verify that the operator is allowed.
    $this->_verifyOperator($operator);
    // Sanitize the column name and store the where clause in the wheres array.
    $sanitizedColumn = $this->queryBuilder->sanitizeName($column);

    // Add the where clause to the wheres array.
    $this->wheres[] = [
      'column' => $sanitizedColumn,
      'operator' => $operator,
      'value' => $value
    ];
    return $this;
  }

  /**
   * Add an order by clause to the query.
   * Allowed directions: ASC, DESC
   */
  function orderBy($column, $direction = 'ASC'): SelectQueryBuilder
  {
    // Verify that the direction is allowed.
    if (!in_array($direction, ['ASC', 'DESC'])) {
      throw new Exception('Invalid direction');
    }

    // Sanitize the column name and store the order by clause in the orders array.
    $sanitizedColumn = $this->queryBuilder->sanitizeName($column);
    // Add the order by clause to the orders array.
    $this->orders[] = [
      'column' => $sanitizedColumn,
      'direction' => $direction
    ];
    return $this;
  }

  /**
   * Limit the number of rows to return.
   */
  function limit($limit): SelectQueryBuilder
  {
    $this->limit = $limit;
    return $this;
  }

  /**
   * Offset the rows to return.
   */
  function offset($offset): SelectQueryBuilder
  {
    $this->offset = $offset;
    return $this;
  }

  /**
   * Return the first row only.
   * OBS: This will set the limit to 1.
   */
  function first(): SelectQueryBuilder
  {
    $this->limit = 1;
    $this->first = true;
    return $this;
  }

  /**
   * Build the query.
   */
  function buildQuery(): mysqli_stmt
  {
    // Convert the columns array into a string to use in the query, e.g. column1, column2, column3.
    $columns = implode(', ', $this->columns);
    // Create the query string, e.g. SELECT column1, column2, column3 FROM table.
    $query = "SELECT $columns FROM $this->table";

    // Add the joins to the query if there are any.
    foreach ($this->joins as $join) {
      // Add the join to the query, e.g. INNER JOIN table ON column1 = column2.
      $query .= " {$join['type']} `{$join['table']}` ON `{$join['column1']}` = `{$join['column2']}`";
    }

    // Add the where clauses to the query if there are any.
    if (!empty($this->wheres)) {
      $query .= ' WHERE ';
      $wheres = [];
      // Add the where clauses to the query, e.g. column1 = ? AND column2 > ?.
      foreach ($this->wheres as $where) {
        $wheres[] = "`{$where['column']}` {$where['operator']} ?";
      }

      // Add the where clauses to the query, e.g. select * from table where column1 = ? AND column2 > ?.
      $query .= implode(' AND ', $wheres);
    }

    // Add the order by clauses to the query if there are any.
    if (!empty($this->orders)) {
      $query .= ' ORDER BY ';
      $orders = [];
      // Add the order by clauses to the query, e.g. column1 ASC, column2 DESC.
      foreach ($this->orders as $order) {
        $orders[] = "`{$order['column']}` {$order['direction']}";
      }

      // Add the order by clauses to the query,
      // e.g. select * from table where column1 = ? AND column2 > ? order by column1 ASC, column2 DESC.
      $query .= implode(', ', $orders);
    }

    // Add the limit and offset to the query if they are set.
    if (isset($this->limit)) {
      $query .= " LIMIT $this->limit";
    }

    if (isset($this->offset)) {
      $query .= " OFFSET $this->offset";
    }

    if (QUERY_BUILDER_SEE_DEBUG) print_r($query . "\n");

    // Prepare the query.
    $statement = $this->queryBuilder->connection->prepare($query);

    // Throw an exception if the query is invalid.
    if (!$statement) {
      throw new Exception($this->queryBuilder->connection->error);
    }

    if (!empty($this->wheres)) {
      $types = '';
      $values = [];
      foreach ($this->wheres as $where) {
        $types .= $this->queryBuilder->getBindValueType($where['value']);
        $values[] = $where['value'];
      }

      // Bind the values to the statement to prevent SQL injection.
      $statement->bind_param($types, ...$values);

      if (QUERY_BUILDER_SEE_DEBUG) {
        print_r($types);
        echo PHP_EOL;
        print_r($values);
        echo PHP_EOL;
      }
    }

    return $statement;
  }

  /**
   * Execute the query against the database and return the result.
   * If ->first() was called, will return the first row only or NULL if no rows were found.
   */
  function execute()
  {
    // Build the query and execute it.
    $statement = $this->buildQuery();
    $statement->execute();
    // Get the result of the query and return it.
    $result = $statement->get_result();
    $allRows = $result->fetch_all(MYSQLI_ASSOC);

    if ($this->first) {
      return count($allRows) > 0 ? $allRows[0] : NULL;
    }

    // Close the statement and return the result.
    $statement->close();
    return $allRows;
  }

  /**
   * Verify that the operator is allowed.
   */
  private function _verifyOperator($operator): void
  {
    if (!in_array($operator, ALLOWED_OPERATORS)) {
      throw new Exception('Invalid operator');
    }
  }
}

class DeleteQueryBuilder
{
  /**
   * The instance of the QueryBuilder class that created this instance.
   */
  private QueryBuilder $queryBuilder;
  /**
   * The table to select from.
   */
  private string $table;
  /**
   * The where clauses.
   */
  private array $wheres = [];
  /**
   * Return first row only.
   */
  private bool $first = false;

  /**
   * Explicitly allow dangerous queries and delete without where.
   * This is a security risk and should be used with caution.
   */
  private bool $allowDangerousQueries = false;

  /**
   * The constructor of the DeleteQueryBuilder class.
   */
  function __construct(QueryBuilder $queryBuilder)
  {
    $this->queryBuilder = $queryBuilder;
  }

  /**
   * Set the table to select from.
   */
  function from($table)
  {
    $this->table = $table;
    return $this;
  }


  /**
   * Add a where clause to the query.
   * Allowed operators: =, !=, >, <, >=, <=, ILIKE, LIKE, NOT LIKE, IN, NOT IN
   */
  function where($column, $operator, $value): DeleteQueryBuilder
  {
    // Verify that the operator is allowed.
    $this->_verifyOperator($operator);
    // Sanitize the column name and store the where clause in the wheres array.
    $sanitizedColumn = $this->queryBuilder->sanitizeName($column);

    // Add the where clause to the wheres array.
    $this->wheres[] = [
      'column' => $sanitizedColumn,
      'operator' => $operator,
      'value' => $value
    ];
    return $this;
  }

  function allowDangerousQueries(): DeleteQueryBuilder
  {
    $this->allowDangerousQueries = true;
    return $this;
  }

  /**
   * Build the query.
   */
  function buildQuery(): mysqli_stmt
  {
    // Create the query string, e.g. DELETE FROM table.
    $query = "DELETE FROM $this->table";

    if (!$this->allowDangerousQueries && empty($this->wheres)) {
      throw new Exception('Dangerous query, delete without where. Use allowDangerousQueries() to allow dangerous queries.');
    }

    // Add the where clauses to the query if there are any.
    if (!empty($this->wheres)) {
      $query .= ' WHERE ';
      $wheres = [];
      // Add the where clauses to the query, e.g. column1 = ? AND column2 > ?.
      foreach ($this->wheres as $where) {
        $wheres[] = "`{$where['column']}` {$where['operator']} ?";
      }

      // Add the where clauses to the query, e.g. delete from table where column1 = ? AND column2 > ?.
      $query .= implode(' AND ', $wheres);
    }

    if (QUERY_BUILDER_SEE_DEBUG) print_r($query . "\n");

    // Prepare the query.
    $statement = $this->queryBuilder->connection->prepare($query);

    // Throw an exception if the query is invalid.
    if (!$statement) {
      throw new Exception($this->queryBuilder->connection->error);
    }

    if (!empty($this->wheres)) {
      $types = '';
      $values = [];
      foreach ($this->wheres as $where) {
        $types .= $this->queryBuilder->getBindValueType($where['value']);
        $values[] = $where['value'];
      }

      // Bind the values to the statement to prevent SQL injection.
      $statement->bind_param($types, ...$values);

      if (QUERY_BUILDER_SEE_DEBUG) {
        print_r($types);
        echo PHP_EOL;
        print_r($values);
        echo PHP_EOL;
      }
    }

    return $statement;
  }

  /**
   * Execute the query against the database and return the result.
   * If ->first() was called, will return the first row only or NULL if no rows were found.
   */
  function execute()
  {
    // Build the query and execute it.
    $statement = $this->buildQuery();
    $statement->execute();
    // Get the result of the query and return it.
    $result = $statement->get_result();
    $allRows = $result->fetch_all(MYSQLI_ASSOC);

    if ($this->first) {
      return count($allRows) > 0 ? $allRows[0] : NULL;
    }

    // Close the statement and return the result.
    $statement->close();
    return $allRows;
  }

  /**
   * Verify that the operator is allowed.
   */
  private function _verifyOperator($operator): void
  {
    if (!in_array($operator, ALLOWED_OPERATORS)) {
      throw new Exception('Invalid operator');
    }
  }
}

class UpdateQueryBuilder
{
  /**
   * The instance of the QueryBuilder class that created this instance.
   */
  private QueryBuilder $queryBuilder;
  /**
   * The table to insert into.
   */
  private string $table;
  /**
   * The columns to insert into.
   */
  private array $columns = [];
  /**
   * The values to insert.
   */
  private array $values = [];
  /**
   * The where clauses.
   */
  private array $wheres = [];

  /**
   * Explicitly allow dangerous queries and update without where.
   * This is a security risk and should be used with caution.
   */
  private bool $allowDangerousQueries = false;

  public function __construct(QueryBuilder $queryBuilder)
  {
    $this->queryBuilder = $queryBuilder;
  }

  /**
   * Set the table to update.
   */
  public function table($table)
  {
    $this->table = $table;
    return $this;
  }

  /**
   * Set the data to update.
   */
  public function set(array $data): UpdateQueryBuilder
  {
    // Sanitize the column names and values and store them in the columns and values arrays.
    foreach ($data as $column => $value) {
      $this->columns[] = $this->queryBuilder->sanitizeName($column);
      $this->values[] = $value;
    }

    return $this;
  }

  /**
   * Set the where clause.
   */
  public function where(string $column, $operator, $value,): UpdateQueryBuilder
  {
    $this->_verifyOperator($operator);

    $this->wheres[] = [
      'column' => $this->queryBuilder->sanitizeName($column),
      'value' => $value,
      'operator' => $operator
    ];

    return $this;
  }

  public function allowDangerousQueries(): UpdateQueryBuilder
  {
    $this->allowDangerousQueries = true;
    return $this;
  }

  /**
   * Build the query.
   */
  public function buildQuery(): mysqli_stmt
  {
    // Convert the columns and values arrays into strings to use in the query, e.g. column1 = ?, column2 = ?, column3 = ?.
    $set = implode(', ', array_map(fn ($column) => "$column = ?", $this->columns));

    // Create the query string, e.g. UPDATE table SET column1 = ?, column2 = ?, column3 = ?.
    $query = "UPDATE $this->table SET $set";

    if (!$this->allowDangerousQueries && empty($this->wheres)) {
      throw new Exception('Dangerous query, update without where. Use allowDangerousQueries() to allow dangerous queries.');
    }

    // Add the where clauses to the query.
    if (count($this->wheres) > 0) {
      $wheres = implode(' AND ', array_map(fn ($where) => "$where[column] $where[operator] ?", $this->wheres));
      $query .= " WHERE $wheres";
    }

    if (QUERY_BUILDER_SEE_DEBUG) print_r($query . "\n");

    // Prepare the query and return the statement.
    $stmt = $this->queryBuilder->connection->prepare($query);
    if (!$stmt) {
      throw new Exception($this->queryBuilder->connection->error);
    }

    // Bind the values to the statement.
    $types = '';
    // Map the where values to an array.
    $whereValues = array_map(fn ($where) => $where['value'], $this->wheres);
    // Merge the values and where values. The first array should be teh update values and the second should be where values.
    $bindValues = array_merge($this->values, $whereValues);
    $values = [];
    // Get the bind value type for each value.
    foreach ($bindValues as $value) {
      $types .= $this->queryBuilder->getBindValueType($value);
      $values[] = $value;
    }
    // Bind the values to the statement.
    $stmt->bind_param($types, ...$bindValues);

    if (QUERY_BUILDER_SEE_DEBUG) {
      print_r($types);
      echo PHP_EOL;
      print_r($bindValues);
      echo PHP_EOL;
    }

    return $stmt;
  }

  /**
   * Execute the query.
   */
  public function execute()
  {
    $stmt = $this->buildQuery();
    $stmt->execute();
    return $stmt;
  }

  /**
   * Verify that the operator is allowed.
   */
  private function _verifyOperator($operator): void
  {
    if (!in_array($operator, ALLOWED_OPERATORS)) {
      throw new Exception('Invalid operator');
    }
  }
}