loading...
Cover image for PHP - Create a QueryBuilder object for the SQL language

PHP - Create a QueryBuilder object for the SQL language

fadymr profile image F.R Michel ・1 min read

PHP version required 7.3

Building SQL Queries with Query Builder

Let's create the class that will build sql language.

<?php

namespace DevCoder;

/**
 * Class QueryBuilder
 */
class QueryBuilder
{
    /**
     * @var array<string>
     */
    private $fields = [];

    /**
     * @var array<string>
     */
    private $conditions = [];

    /**
     * @var array<string>
     */
    private $from = [];

    public function __toString(): string
    {
        $where = $this->conditions === [] ? '' : ' WHERE ' . implode(' AND ', $this->conditions);
        return 'SELECT ' . implode(', ', $this->fields)
            . ' FROM ' . implode(', ', $this->from)
            . $where;
    }

    public function select(string ...$select): self
    {
        $this->fields = $select;
        return $this;
    }

    public function where(string ...$where): self
    {
        foreach ($where as $arg) {
            $this->conditions[] = $arg;
        }
        return $this;
    }

    public function from(string $table, ?string $alias = null): self
    {
        if ($alias === null) {
            $this->from[] = $table;
        } else {
            $this->from[] = "${table} AS ${alias}";
        }
        return $this;
    }
}

Enter fullscreen mode Exit fullscreen mode

How to use ?

$query = (new QueryBuilder())
->select('email', 'first_name', 'last_name')
->from('user');

$pdoStatement = $pdo->prepare($query);
$pdoStatement->execute();

$users = $pdoStatement->fetchAll(\PDO::FETCH_ASSOC);
Enter fullscreen mode Exit fullscreen mode
$query = (new QueryBuilder())
->select('u.email', 'u.first_name', 'u.last_name', 'u.active')
->from('user', 'u')
->where('u.email = :email', 'u.active = :bool');

$pdoStatement = $pdo->prepare($query);
$pdoStatement->execute([
    'email' => 'dev@devcoder.xyz', 
    'bool' => 1
    ]
);

$user = $pdoStatement->fetch(\PDO::FETCH_ASSOC);
Enter fullscreen mode Exit fullscreen mode

Ideal for small project
Simple and easy!
https://github.com/devcoder-xyz/php-query-builder

How can we improve this object?

  • add methods join, limit, offset, groupBy etc..
  • Create insert, update and delete statement

Discussion

pic
Editor guide