Bind IS NULL or NULL when using PHP PDO and MySql

Question

How can I bind NULL in the following scenario. No matter what I do, I can't get it to work.

if ($type == 1) {
    $self   = 'NULL';
    $parent = 'NULL';
}

$findThis = $conn->prepare('select count(id) as exists from table where id = :id and self = :self and parent = :parent';

$findThis->execute(array(
    ":id"     => $id,
    ":self"   => $self,
    ":parent" => $parent
));

If you need to use nulls, you use $stmt->bindValue(':placeholder', null, PDO::PARAM_NULL);. If you can avoid using nulls, use SQL's IS NULL etc. — N.B.
– N.B., Commented Sep 11, 2013 at 8:56
@YourCommonSense - well feel free to correct if it's wrong, so people can see what's correct and what isn't, this way we're left hanging :) — N.B.
– N.B., Commented Sep 11, 2013 at 9:06
The reason I wrote the comment was to show how to use null values in queries (you might want to insert a null). That's why it's a comment and not an answer. Naturally, since answers tackle the problem properly - there was no need to repeat the same. Also, I'll leave the comment there just in case someone stumbles upon this and for some odd reason can't determine how to handle null values upon inserting. Answers are sufficient, the example in my comment is wrong for this particular usage scenario and I'll leave it in so people don't make the same mistake. — N.B.
– N.B., Commented Sep 11, 2013 at 9:28

Your Common Sense · Accepted Answer · 2013-12-04 11:17:32Z

3

UPDATE. By an by I have learned that all can be done in one query

$sql = 'select 1 from table where id = ? and self <=> ? and parent <=> ?';
$stm = $conn->prepare($sql);
$stm->execute([$id,$self,$parent]);
$exists = $stm->fetchColumn();

~~You have to amend your query as well.~~

$sql = 'select count(id) as exists from table where id = ? and '; if ($type == 1) { $sql .= 'self IS NULL and parent IS NULL' $data = [$id]; } else { $sql .= 'self = ? and parent = ?'; $data = [$id,$self,$parent]; } $stm = $conn->prepare($sql); $stm->execute($data);

edited Dec 4, 2013 at 11:17

answered Sep 11, 2013 at 8:48

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xdazz · Accepted Answer · 2013-09-11 08:18:47Z

1

For null values, you have to use IS NULL in the sql instead.

answered Sep 11, 2013 at 8:18

xdazz

161k38 gold badges255 silver badges278 bronze badges

Collectives™ on Stack Overflow

Bind IS NULL or NULL when using PHP PDO and MySql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related