Create a Query Builder with PHP and SQL

There's many ways to use SQL with PHP and certainly the way to go, even without an ORM or a structured query builder is with PDO. This example utilizes POO philosophy and PDO system.

First we'll initialize the QueryBuilder class utilizing some libraries that already come with PHP such as Exception, PDOException, stdClass and PDO itself.

Then we'll create the class with a bunch of keywords that'll help us down the road.


<?php

namespace App\Core;

use Exception;
use PDO;
use PDOException;
use stdClass;

class QueryBuilder
{
    private $model;
    private $pk;
    private $required;
    private $timestamps;
    protected $stmt;
    protected $params;
    protected $group;
    protected $data;

    public function __construct(string $model, array $required, string $pk = 'id', bool $timestamps = true)
    {

        $this->model = $model;
        $this->pk = $pk;
        $this->required = $required;
        $this->timestamps = $timestamps;

    }
}

Our construct function will always need a model/entity, a Primary Key, the required information and the timestamps. You can tweak this later according to your needs.

Now we'll create our getter, setter and isset methods. I want to make sure we'll receive objects as response.

public function __set($name, $value)
{
    if (empty($this->data)) {
        $this->data = new stdClass();
    }

    $this->data->$name = $value;
}

public function __isset($name)
{
    return isset($this->data->$name);
}

public function __get($name)
{
 return ($this->data->$name ?? null);
}

Since we have the basics down, we'll create the first type of SQL queries: finders.

public function find(?string $terms = null,
?string $params = null, string $columns = "*") : QueryBuilder
{
    if ($terms) {
        $this->stmt = "SELECT {$columns} FROM {$this->model} WHERE {$terms}";
        parse_str($params, $this->params);
        return $this;
    }

    $this->stmt = "SELECT {$columns} FROM {$this->model}";
    return $this;
}

public function findById(int $id, string $columns = "*") : ?QueryBuilder
{
    return $this->find("{$this->pk} = :id", "id={$id}", $columns)->fetch();
}

The find() method creates an statement from the information we'll pass onto parameters (except the model, since we'll access it like this:

php $model->find($params)

). Then findById() calls find but extends the where functionality to what we want to.

Now let's fetch information.

public function fetch(bool $all = false)
{
    try {
         $stmt = Connection::getInstance()->prepare($this->stmt . $this->group . $this->order . $this->limit . $this->offset);
         $stmt->execute($this->params);

        if (!$stmt->rowCount()) {
            return null;
        }

        if ($all) {
            return $stmt->fetchAll(PDO::FETCH_CLASS, static::class);
        }

        return $stmt->fetchObject(static::class);
     } catch (PDOException $exception) {
         return exception;
     }
}

Since you already noticed, I'm accessing the Connection object that simply handles the connection with Database. There's a bunch of tutorials online regarding this topic, so I won't extend myself.

Your DB connection should be separate from your queries to the database.

Inside the fetch method, I'm accessing PDO's own methods to make the actual queries, and I'm being cautious as to what I'm doing with the information. Everytime something might break, I'll add the corresponding exception.

I'll also need to delete records eventually, so I'll past the code for it below:

public function delete(string $terms, ?string $params): bool
{
    try {
        $stmt = Connection::getInstance()->prepare("DELETE FROM {$this->model} WHERE {$terms}");

        if ($params) {
            parse_str($params, $params);
            $stmt->execute($params);
            return true;
        }

        $stmt->execute();
        return true;
    } catch (\PDOException $exception) {
        return $exception;
    }
}

And lastly, we'll need our create method.

public function create(array $data): ?int
{
    try {

        $columns = implode(", ", array_keys($data));
        $values = ":" . implode(", :", array_keys($data));
        $stmt = Connection::getInstance()->prepare("INSERT INTO {$this->model} ({$columns}) VALUES ({$values})");
        $stmt->execute($this->data);

        return Connection::getInstance()->lastInsertId();
    } catch (\PDOException $exception) {
        return $exception;
    }
}

I hope you enjoyed this tutorial and that it helped in your adventures with PHP, SQL and PDO.

Blog

Create a Query Builder with PHP and SQL

Daepher

Join Our Newsletter. No Spam, Only the good stuff.

Related