PHP - Create a QueryBuilder object for the SQL language

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;
    }
}

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);

$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' => '[e-mail]', 
    'bool' => 1
    ]
);

$user = $pdoStatement->fetch(\PDO::FETCH_ASSOC);

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

Comments

[MaelStorm]

Bonjour je trouve le query builder vraiment top !

Malheureusement je ne comprends pas comment utiliser les Insert. Je suis débutant, voici la portion de code que je ne comprends pas : dev-to-uploads.s3.amazonaws.com/up...

[Boss]

Thanks for the post, so how about select on multiple table?