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.
Top comments (7)
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
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.
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!
@darkain would you like to share your beast brother?
You can find it here: pudl.dev/
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.
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 :)