CODEWITHSUNDEEP
phpsqlnullpdo
Ian
Ian

Reputation: 25366

PDO Prepared Statements - NULLs

I have a delete query that I'm running, but just realized that this doesn't work when $user_id is null (which happens in certain cases).

$id = 1;
$user_id = null;
$delete = $sql->prepare("
    DELETE FROM
        `game_player`
    WHERE
        `id` = ?
    AND
        `user_id` = ?
");
if ($delete->execute(array(
    $id,
    $user_id,
));

Is there any work around other than having different queries for when the value is null, since apparently the only way to have the where work properly is with user_id IS NULL instead of user_id = NULL...

Upvotes: 8

Views: 1236

Answers (2)

goat
goat

Reputation: 31854

DELETE FROM
    `game_player`
WHERE
    `id` = ?
AND
    (`user_id` = ? OR ? IS NULL)

Be careful to parenthesis properly when mixing and with or operators.

If $user_id isn't really php type null, but say, an empty string, you should modify the above as such:

...
AND
    (`user_id` = ? OR ? = '')

Upvotes: 6

Holtorf
Holtorf

Reputation: 1491

A quick or statement solves the problem. Change this bit of code.

$delete = $sql->prepare(
    "DELETE_FROM 'game_player' where 'id'=? and 'user_id'=?;");
if ($delete->execute(array(
    $id,
    $user_id
));

Into something more like the following. 

$delete = $sql->prepare(
    "DELETE_FROM 'game_player' where ('id'=? and 'user_id'=?) 
                                  or ('id'=? AND 'user_id' IS NULL)");
if ($delete->execute(array(
    $id,
    $user_id,
    $id
))) { /* success */ }

Upvotes: 0

Related Questions