DEV Community

Micael Vinhas
Micael Vinhas

Posted on

Write your first QueryBuilder with PDO and PHP

#pdo #sql #php #querybuilder

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

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

But first, we will cover something much more basic

SELECT * FROM customers WHERE name = 'David' LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

With this

(new Select)->from('customers')->where(['name', '=', 'David'])->limit(5)
Enter fullscreen mode Exit fullscreen mode

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

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

}
Enter fullscreen mode Exit fullscreen mode 
<?php

class Select extends Db
{
    public function from(string $table) {}
    public function where(array $condition) {}
    public function limit(int $limit = 1) {}
    public function fetch() {}
}
Enter fullscreen mode Exit fullscreen mode

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

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

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

To get something like this:

Array
(
    [0] => stdClass Object
        (
            [id] => 3
            [name] => David
        )

    [1] => stdClass Object
        (
            [id] => 4
            [name] => David
        )

)
Enter fullscreen mode Exit fullscreen mode

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

And here is a potential approach:

(new Select('id'))->from('customers')->where(['name','=','"David"'])->limit(5);
Enter fullscreen mode Exit fullscreen mode

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

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

The rest stays exactly the same, and here is the produced result:

Array
(
    [0] => stdClass Object
        (
            [id] => 3
        )

    [1] => stdClass Object
        (
            [id] => 4
        )

)
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

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

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

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

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

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

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

}
Enter fullscreen mode Exit fullscreen mode

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

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

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!

Top comments (1)

Collapse
 
ravavyr profile image
Ravavyr •

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

Read next

firoj_mujawar profile image

SQL Performance: A Deep Dive into Indexing with examples

Firoj Mujawar -

mrcaption49 profile image

Stored procedure in PLSQL

Pranav Bakare -

tigawanna profile image

supabase functions (not edge)

Dennis kinuthia -

servbay profile image

How to Migrate NGINX Website to ServBay

ServBay -