PDO Prepared Statements - NULLs

Question

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...

I would think IS NULL is clean cut, and more effectively

ajreal
– ajreal

2011-01-05 02:47:08 +00:00
Commented Jan 5, 2011 at 2:47 — ajreal
– ajreal, Commented Jan 5, 2011 at 2:47

goat · Accepted Answer · 2012-09-21 15:53:33Z

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 ? = '')

smdvlpr · Accepted Answer · 2012-03-24 15:27:10Z

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 */ }

Collectives™ on Stack Overflow

PDO Prepared Statements - NULLs

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related