DEV Community

Cover image for Create a Query Builder with PHP and SQL
Daepher
Daepher

Posted on

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;

    }
}

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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;
     }
}
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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

Discussion (5)

Collapse
darkain profile image
Vincent Milum Jr

If you're interested in something a little more feature complete, I'd suggest checking out PUDL (PHP Universal Database Library). It contains connection manager (including clustering support), SQL query generation, data transformation, and tons more. It also has data sanitization built in, and works with countless databases engines (MySQL, MariaDB, SQL Server, PostgreSQL, and several more)

github.com/darkain/pudl

Collapse
daepher profile image
Daepher Author

That's a nice project! I decided to create my Query Builder as small as possible, but I didn't cover all the features in this post.

Collapse
darkain profile image
Vincent Milum Jr

My goal when I built mine too was to create something "as small as possible", but over the course of a decade, needs continued to grow! hahahaaaa When it first started, it was a simple single file with just a couple of helper functions, no classes. Now its a beast that can handle tons of stuff!

Collapse
elvinas profile image
Elvinas

I quite like your Query Builder class, but I have noticed some simple mistakes which should be easy to fix and to understand.

On fetch() method you wrote "return exception;" it should be return "$exception;"
On delete() method you provided return type "bool" in case where PDOException happens you return $exception; in this instance it would return PDOException so it never will be a boolean when something fails. Also the same thing applies for create() method.

Collapse
daepher profile image
Daepher Author

Thank you for your feedback!

Yes, I noticed I've made a few spelling errors and missed some other stuff. I'll revise it, thanks a lot for the help :)