PDO integer nullable value issue

Question

I have a code block like this ;

$a='';

$Query =$db->prepare("UPDATE TblUsers
SET Age = :age
WHERE
 IsActive=1 ")
$Query->bindParam(":age",$a,PDO::PARAM_INT);
$Query->execute();

When i run this code i get this error ;

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'Age' at row 1'

But it will be nullable variable when i add '' . but i get this error. Where is my fail ?

post the schema and the value for the intended value for `$a`. Right now, there's no value for it. Or, allow empty/NULL values. — Funk Forty Niner, Aug 21 '17 at 11:32
If you want the value of `age` to be null sometines then you have to set it to `null` instead of `''` — juergen d, Aug 21 '17 at 11:32
The error is pretty clear. The String of age is empty and the query expects a value int — Noob, Aug 21 '17 at 11:33
I Want to change empty string to null variable. How can i do it with PDO ? There is any option it or i need write a function about it ? — Zibian Domichi, Aug 21 '17 at 11:34
@ZibianDomichi you need to make column `default-value` `null` in your db table — Alive to die - Anant, Aug 21 '17 at 11:35
@AlivetoDie its working but i dont want it . I want to solve it with pdo. — Zibian Domichi, Aug 21 '17 at 11:37
ALTER your table instead and as [per my first comment](https://stackoverflow.com/questions/45796027/pdo-integer-nullable-value-issue#comment78549559_45796027). *"I want to solve it with pdo"* - why do you feel the need to do this with PDO? — Funk Forty Niner, Aug 21 '17 at 11:39
Mayde there is any option about it Like $db->options(PDO::ATTR_NULLINTEGER); — Zibian Domichi, Aug 21 '17 at 11:40
Check this : https://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo/18989269 — Niklesh Raut, Aug 21 '17 at 11:41

score 1 · Accepted Answer · answered Aug 21 '17 at 11:42

You need to use bindValue like below:-

$Query =$db->prepare("UPDATE TblUsers
SET Age = :age
WHERE
 IsActive=1 ")
$Query->bindValue(':age', $a, PDO::PARAM_INT);
$Query->execute();

bindParam takes a variable, to reference, and doesn't pull in a value at the time of calling bindParam.

Referecne:-How do I insert NULL values using PDO?

PDO integer nullable value issue

1 Answers1