How to create a WHERE clause for PDO dynamically

  1. Create the query dynamically
  2. Create a static query with all conditions at once
  3. Comments (4)

This is quite a common task when we need to create a search query based on the arbitrary number of parameters.

The main problem here is that we cannot create a query beforehand (well, actually we can and it will be shown later) and therefore we don't know which parameters to bind.

Create the query dynamically

PDO doesn't have a functionality for this out of the box, but it can ease the task significantly, thanks again to its ability to bind the arbitrary number of parameters in the form of array passed to execute(). So we can use a conditional statement which will add a condition to the query and also a parameter to bind:

// always initialize a variable before use!
$conditions = [];
$parameters = [];

// conditional statements
if (!empty($_GET['name']))
{
    
// here we are using LIKE with wildcard search
    // use it ONLY if really need it
    
$conditions[] = 'name LIKE ?';
    
$parameters[] = '%'.$_GET['name']."%";
}

if (!empty(
$_GET['sex']))
{
    
// here we are using equality
    
$conditions[] = 'sex = ?';
    
$parameters[] = $_GET['sex'];
}

if (!empty(
$_GET['car']))
{

    
// here we are using not equality
    
$conditions[] = 'car != ?';
    
$parameters[] = $_GET['car'];
}

// the main query
$sql "SELECT * FROM users";

// a smart code to add all conditions, if any
if ($conditions)
{
    
$sql .= " WHERE ".implode(" AND "$conditions);
}

// the usual prepare/execute/fetch routine
$stmt $pdo->prepare($sql);
$stmt->execute($parameters);
$data $stmt->fetchAll();

Create a static query with all conditions at once

We can actually use a conditional statement right in the query. A condition like (name = :name or :name is null) will return true if either the value matches the field's contents or if the value is null. It means that if we want the query to bypass a condition, we just have to pass null for the value. ong story short, here it goes:

$parameters['name'] = !empty($_GET['name']) ? $_GET['name'] : null;
$parameters['sex']  = !empty($_GET['sex'])  ? $_GET['sex']  : null;
$parameters['car']  = !empty($_GET['car'])  ? $_GET['car']  : null;

$sql "SELECT * FROM users 
WHERE (name = :name or :name is null)
AND   (sex  = :sex  or :sex  is null)
AND   (car  = :car  or :car  is null)"
;

$stmt $pdo->prepare($sql);
$stmt->execute($parameters);
$data $stmt->fetchAll();

The only drawback here is that this code will work only if emulation is turned off


Related articles: