Write your first QueryBuilder with PDO and PHP
Tags: pdo, sql, php, querybuilder
Published: 2022-08-16 13:47:00

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](https://www.php.net/manual/en/migration56.new-features.php) 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](https://github.com/MVinhas/querybuilder_php_pdo)!

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!