Take this article as storytelling, where you start with the very basics and then improve your code as you move on.
Grab a cup of coffee if you didn't do it already.
Understand the importance of writing cleaner queries
Writing the raw SQL is not necessarily hard or inconvenient most of the time, but as soon as your application takes off and goes big, you will have a bunch of long strings with SQL code that simply does not go along with the other PHP code.
Writing a query builder makes the code much more seamless and readable. Also, you will be less error-prone, because the query building will prepare the query in a consistent way.
In this article, we will approach SELECT statements.
SELECT statements
By the end of this article, your application should run the following query
SELECT customers.id FROM customers LEFT JOIN persons ON customers.person_id = persons.id WHERE customers.name = 'David' AND persons.id = 1 LIMIT 1
With this code
(new Select('customers.id'))
->from('customers LEFT JOIN persons')
->on(['customers.person_id','=','persons.id'])
->where(['customers.name', '=', '"David"'])
->limit(1)->fetch()
But first, we will cover something much more basic
SELECT * FROM customers WHERE name = 'David' LIMIT 5;
With this
(new Select)->from('customers')->where(['name', '=', 'David'])->limit(5)
Looking at the above code, we already know a couple of things:
- We have a class called Select
- The class has, at least, three methods. They are from, where and limit
- One method should be responsible to make the query. Let's call it fetch
This is enough to write the base class:
class Select
{
public function from(string $table) {}
public function where(array $condition) {}
public function limit(int $limit = 1) {}
public function fetch() {}
}
Looking great, but we are not making any actual query, we are only writing the SQL statement. Making the query itself obliges us to create a PDO Connection (for example).
If we create this initialization code on the Select class, we have to do it over and over again for any other database operation, like insert or update. So, let's create another class called Db and extend it on Select.
<?php
use PDO;
use PDOException;
class Db
{
public static function initialize()
{
try {
return new PDO(
'mysql:host=localhost;dbname=db',
'user',
'password'
);
} catch (PDOException $e) {
die("Could not connect to the database: $e");
}
}
}
<?php
class Select extends Db
{
public function from(string $table) {}
public function where(array $condition) {}
public function limit(int $limit = 1) {}
public function fetch() {}
}
A good basis to work with.
Now, we need to create three variables: $from, $where, and $limit. These variables will save the different parts of the query we want to do. There are many more for a SELECT query, but it's wise to start with the basics and then work our way up to create more complex queries.
public $from;
public $where;
public $limit;
All declarations are set, time to fill the methods with code
public function from(string $table)
{
$this->from = "SELECT * FROM $table";
return $this;
}
public function where(array $condition)
{
$this->where = "WHERE $condition[0] $condition[1] $condition[2]";
return $this;
}
public function limit(int $limit = 0)
{
$this->limit = "LIMIT $limit";
return $this->fetch();
}
public function fetch()
{
$sql = implode(' ', (array)$this);
$db = self::initialize();
$db->beginTransaction();
$query = $db->prepare($sql);
$db->commit();
$query->execute();
return $query->fetchAll(PDO::FETCH_OBJ);
}
Finally, a call on index.php
<?php
require_once 'Select.php';
require_once 'Db.php';
$query = (new Select)->from('customers')->where(['name', '=', 'David'])->limit(5);
echo "<pre>";print_r($query);"</pre>";
To get something like this:
Array
(
[0] => stdClass Object
(
[id] => 3
[name] => David
)
[1] => stdClass Object
(
[id] => 4
[name] => David
)
)
We have the foundations, but our code is too basic right now. We cannot select a particular field, or make multiple wheres, aside from other constraints.
I have a couple of ideas to make our Select class richer and more featureful. Use the comments section to throw some more ideas.
- Select particular fields
- Multiple wheres
- Joins
- Discard the mandatory use of limit
Select particular fields
In the above example, we already know that the name we want to query is David, so grabbing the id only should be enough.
This is the query we want to make:
SELECT id FROM customers WHERE name = 'David' LIMIT 5
And here is a potential approach:
(new Select('id'))->from('customers')->where(['name','=','"David"'])->limit(5);
We are passing id as a parameter of the class, so we need to implement this code on the constructor.
(Don't feel lost. I will paste the complete code at the end of this article.)
On class Select.php:
public $fields;
public function __construct(string $fields = '*')
{
$this->fields = "SELECT $fields";
}
Since the constructor is now responsible to start the query, we have to adapt our from function.
public function from(string $table) {
$this->from = $table;
return $this;
}
The rest stays exactly the same, and here is the produced result:
Array
(
[0] => stdClass Object
(
[id] => 3
)
[1] => stdClass Object
(
[id] => 4
)
)
Which is exactly want we want.
Moving on.
Multiple wheres
You may have noticed that we are passing a unidimensional array to the where function. So, for the following query...
SELECT * FROM customers WHERE name = 'David' AND id = '3' LIMIT 1
...how can we write the query builder to accommodate two clauses on where?
There are a couple of possible solutions, but, I like the CodeIgniter way:
(new Select)
->from('customers')
->where(['name','=','"David"'],'AND',['id','=','3'])
->limit(1);
This is not 100% equal to how CodeIgniter implements their where function, but for this article, I think it's the most comprehensive way.
We need to use array unpacking because we don't know the number of parameters needed. You may only need to use one clause, or twenty.
Let's try to make it work:
public function where(...$conditions)
{
$where[] = 'WHERE';
foreach ($conditions as $condition) {
$where[] =
is_array($condition) ?
implode(' ', $condition) :
$condition;
}
$this->where = implode(' ', $where);
return $this;
}
Joins
Joins are very useful and usually help us to get everything we want on a single query.
They will also bring more complexity to our code, but not that much.
For the following query:
SELECT customers.id FROM customers LEFT JOIN persons ON customers.person_id = persons.id WHERE customers.name = 'David' AND persons.id = 1 LIMIT 1
Our query builder can look like this:
(new Select('customers.id'))
->from('customers LEFT JOIN persons')
->on(['customers.person_id','=','persons.id'])
->where(['customers.name', '=', '"David"'])
->limit(1);
Look at the new function, on. Notice something familiar? Yes, it is very similar to where, so why don't we reuse some code?
public $on;
public function where($conditions)
{
$this->where = $this->clause('WHERE', $conditions);
return $this;
}
public function on($conditions)
{
$this->on = $this->clause('ON', $conditions);
return $this;
}
public function clause(string $prefix, ...$conditions)
{
$array[] = $prefix;
foreach ($conditions as $condition) {
$array[] = is_array($condition) ?
implode(' ', $condition) :
$condition;
}
return implode(' ', $array);
}
One more step and we are done!
Discard the mandatory use of limit
If we don't call limit, our query will not be done, ever. This is because we request the fetch on the limit function, which is not ideal.
Why complicate? The solution is in the front of our eyes. We already have a class called fetch, so why don't we call it?
(new Select('customers.id'))
->from('customers LEFT JOIN persons')
->on(['customers.person_id','=','persons.id'])
->where(['customers.name', '=', '"David"'])
->limit(1)->fetch();
You only need to make a tiny change on the limit function, because we don't want to call fetch from there anymore:
public function limit(int $limit = 1) {
$this->limit = "LIMIT $limit";
return $this;
}
Bonus: orderBy
Since we are using LIMIT 1, there is no need to use ORDER BY, but you may need it in the future.
The solution is very similar to the limit function, except in this case we have to accept two parameters:
public function orderBy(string $field = '', string $order = 'ASC')
{
if (empty($field)) return $this;
$this->orderBy = "ORDER BY $field $order";
return $this;
}
Wrapping up
To see this query builder in action, create three files: index.php, Db.php, and Select.php, and then paste the following code:
Db.php
<?php
use PDO;
use PDOException;
class Db
{
public static function initialize()
{
try {
return new PDO(
'mysql:host=localhost;dbname=db',
'user',
'password'
);
} catch (PDOException $e) {
die("Could not connect to the database: $e");
}
}
}
Select.php
<?php
require_once 'Db.php';
class Select extends Db
{
//Declare the variables in this order
public $fields;
public $from;
public $on;
public $where;
public $orderBy;
public $limit;
public function __construct(string $fields = '*')
{
$this->fields = "SELECT $fields";
}
public function from(string $table) {
$this->from = "FROM $table";
return $this;
}
public function where($conditions)
{
$this->where = $this->clause('WHERE', $conditions);
return $this;
}
public function on($conditions)
{
$this->on = $this->clause('ON', $conditions);
return $this;
}
public function clause(string $prefix, ...$conditions) : string
{
$array[] = $prefix;
foreach ($conditions as $condition) {
$array[] = is_array($condition) ?
implode(' ', $condition) :
$condition;
}
return implode(' ', $array);
}
public function orderBy(string $field = '', string $order = 'ASC')
{
if (empty($field)) return $this;
$this->orderBy = "ORDER BY $field $order";
return $this;
}
public function limit(int $limit = 1) {
$this->limit = "LIMIT $limit";
return $this;
}
public function fetch() {
$sql = implode(' ', (array)$this);
$db = self::initialize();
$db->beginTransaction();
$query = $db->prepare($sql);
$db->commit();
$query->execute();
return $query->fetchAll(PDO::FETCH_OBJ);
}
}
index.php
<?php
require_once 'Select.php';
require_once 'Db.php';
$query = (new Select('customers.id'))
->from('customers LEFT JOIN persons')
->on(['customers.person_id','=','persons.id'])
->where(['customers.name', '=', '"David"'])
->limit(1)->fetch();
echo "<pre>";print_r($query);"</pre>";
Remember to create the tables customers and persons, and, of course, the proper database (in this example: db). Change the code to suit your data if you want.
Looking for our GitHub repository? Here it is!
Further steps?
There are always things to improve. For example, we can create some error messages to bring more feedback to the user when something is not ok.
And many more things, like subqueries and unions. If you think that it's a good idea to fill our class with more functionality, let me know in the comments.
Stay tuned!
Latest comments (1)
Query builders are a fun exercise in futility :)
Over the years I think i've probably seen 5 to 10 of these things, even wrote at least one or two myself.
In the end, just writing the damn SQL queries is so much more efficient and easier to debug. Query builders are absolutely not worth the time and effort.
And yes, I know you need to debug and secure your own queries. That should be standard practice anyway.
I'll admit the challenge of building a good general use query builder is fun though.
Kudos