Create a dynamic WHERE clause with OOP PHP. How to implement BETWEEN operator?

Question

Im trying to create a dynamic WHERE clause builder with PHP OOP. And Im not sure what Im doing is right.

So I created a class named QueryBuilder where I have a private property named $filters (this is an array property for store both keys and values for filters) and two methods:

1, addFilter() -> This is for adding "filters" to the query

2, getFilters() -> This is for getting the WHERE clause string and the params for PDO execute()

Here is my code:

class QueryBuilder {

 private array $filters;

 public function addFilter(array $filterDetails): void
 {
    $this->filters[] = $filterDetails;

 }

public function getFilters(): array
 {
    $whereClause = " WHERE 1";
    if (!empty($this->filters)) {
        foreach ($this->filters as $filter) {
            $whereClause .= " AND (" . $filter["searchKey"] . " " . $filter["operator"] . " :" . $filter["searchKey"] . ")";
            $params[":" . $filter["searchKey"]] = $filter["searchValue"];
        }
        return ["whereClause" => $whereClause,
            "params"              => $params];
    }
    return ["whereClause" => $whereClause,
        "params"              => null];
  }

 }

And this is how I run it:

$QueryBuilder = new QueryBuilder();
$sql = "SELECT * FROM mytable";
$name = "Test Name";
$age = 12;
$QueryBuilder->addFilter(["operator" => "LIKE",
    "searchKey"                            => "name",
    "searchValue"                          => $name]);
$QueryBuilder->addFilter(["operator" => "=",
    "searchKey"                            => "age",
    "searchValue"                          => $age]);


$whereClause = $QueryBuilder->getFilters();
echo $sql . $whereClause["whereClause"];
echo "</br>";
echo "<pre>";
print_r($whereClause["params"]);
echo "</pre>";

The results seems to be fine:

SELECT * FROM mytable WHERE 1 AND (name LIKE :name) AND (age = :age)
Array
(
    [:name] => Test Name
    [:age] => 12
)

But now Im trying to create a WHERE clause where is a BETWEEN operator and I don't have any idea how can I implement into this code.

Anyway what do you think this is a good approach to create a query (WHERE CLAUSE) builder? Or should I make some modifications to the code?

Thank you!

Answer 1

Instead of having just one addFilter() method you could have specific methods for different things:

public function addWhereLike($columnName, $value);
public function addWhereEqual($columnName, $value);
public function addWhereBetween($columnName, $minimumValue, $maximumValue);

And so on. You should now process the method arguments inside these methods, and store the resulting strings in the QueryBuilder object, not process them all together in the getFilters() method. addWhereLike() and addWhereEqual() could both use a private method addWhereWithOperator() that takes the operator as an extra argument.

I would also rename getFilters() into getWherePartOfQuery(). The name should reflect what it does.

I'm also sure that, in the end, you want your QueryBuilder() to actually build whole queries, and not just the WHERE bit, so keep that in mind.

NOTE: Just to be clear, this answer answers your question, but I'm not saying this is the best solution for a query builder. There have been many attempts at this, some more successful than others. The term you probably want to look for is: ORM. Although technically this term does not always relate to databases, it often does.