通过mysql上的变量发送“null”不会删除该行

Question

I have this code/query:

if ($a == $b) {
    $type = 1;
} else {
    $type = null;
}

$this->query(
    'DELETE FROM users WHERE userid = ? AND type = ?',
    array($userid, $type)
);

Buy mysql doesn't understand the second case and won't delete the row if $type is null.

I know that a proper way to define null in a query would be with IS NULL, but in my case, it won't fit.

Can I somehow pass null through a variable?

Answer 1

It's certainly possible to make the WHERE clause itself aware switch between the value and IS NULL but requires additional conditions and additional placeholders. In your case, since the query is simple, I would build it dynamically and append parameters to the array() for execution accordingly.

// The basic query, with conditions common to either case:
$sql = 'DELETE FROM users WHERE userid = ? AND ';

// For bound parameters...
// $userid will always be present
$params = array($userid);
if ($a == $b) {
  // Add an additional parameter for type
  $params[] = 1;
  // And add to the WHERE clause the type condition
  $sql .= ' type = ?';
}
else
  // Or the NULL  type condition
  $sql .= 'type IS NULL';
}
// Execute with the $params array, which has 1 or 2 elements.
$this->query($sql, $params);

In order to stuff this into one query, the WHERE clause would have to detect that the type variable was non-null with an OR condition. One way (not the only way, and maybe not the nicest way) to do it looks like this:

DELETE
FROM users
WHERE
  userid = ?
  AND ((? = 1 AND type = ?) OR type IS NULL)

In this case, the value of $type would be passed in twice, first to make the condition 1=1 true, then to actually compare with the type column. But as you can see, when using placeholders, this becomes sort of confusing. It is easier to just build the string dynamically in the first place.