Building MySQL query with need for variable operators PHP PDO

Question

Is there any standard or best way in PHP PDO database class to change the operator from "=" to "IS" when I don't know if the value I'm going to pass is NULL or not. Here is an example of what I mean.

I have two variables. I want a count of the results that match. Sometimes one or both of the variables can be NULL. But MySQL requires a different operator to find NULL (IS NULL).

I'm currently just creating another variable for the operator and when I build the query I inject it in as so:

  // First create a variable "=" or "is" depending on whether var1 and var2 are NULL or not
  if($var1 == NULL) $var1_operator = "IS";
  else $var1_operator = "=";
  if($var2 == NULL) $var2_operator = "IS";
  else $var2_operator = "=";

  // Build the query
  $query = 'SELECT count(*) as count FROM table 
  WHERE var1 '.$var1_operator.' :var1 AND var2 '.$var2_operator.' :var2';

  //  Conduct the query
  $prepare = $db_connection->prepare($query);
  $prepare->bindValue(':var1', $var1, PDO::PARAM_STR);
  $prepare->bindValue(':var2', $var2, PDO::PARAM_STR);
  $prepare->execute();
  $results = $prepare->fetch();

Is there another way to do this? This works to bindValue using PARAM_STR even when the value is NULL and not really a string. The query returns the correct value count. Just not sure if there is better practice for doing it.

Your Common Sense Your Common Sense · Accepted Answer · 2017-12-07T08:01:16

As you are using mysql, you can use its spaceship operator, <=>:

$query = 'SELECT count(*) FROM table WHERE var1 <=> ? AND var2 <=> ?';
$prepare = $db_connection->prepare($query);
$prepare->execute([$var1, $var2]);
$count = $prepare->fetchColumn();

But in general, if you need variable operators or other query parts, such a conditional query building is the only way.

A more generalized solution can be found in my article How to create a WHERE clause for PDO dynamically:

// 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();

so in these conditions above whatever logic could be implemented, including operator change or anything.

Building MySQL query with need for variable operators PHP PDO

1 Answers