MySql and PDO: How to send a null param with bindParam()

Question

The value of the param $isConfirmed can be null, 0 or 1. When I try to send "null", PDO is returning me an Exception. How can I resolve that problem ?

$dbHandler = $dbConnection->prepare("INSERT INTO `Group` (visitId, guideId, groupSize, isConfirmed) VALUES (:visitId, :guideId, :groupSize, :isConfirmed)");
        for($i = 0; $i < count($groupId); $i++) { 
                $dbHandler->bindParam(":visitId", $visitId, PDO::PARAM_INT); 
                $dbHandler->bindParam(":guideId", $guideId, PDO::PARAM_INT); 
                $dbHandler->bindParam(":groupSize", $groupSize, PDO::PARAM_INT); 
                $dbHandler->bindParam(":isConfirmed", $isConfirmed, PDO::PARAM_INT); 
                $dbHandler->execute(); 
        }

If it's a null, it has to be PDO::PARAM_NULL, not PDO::PARAM_INT. Do a check to determine what type it is. — N.B.
– N.B., Commented Oct 21, 2013 at 14:07

Your Common Sense · Accepted Answer · 2013-11-04 19:56:15Z

2

Update
Well, I was a fool, with all my patronizing.

There actually no need for messing with param type. PDO automatically binds NULL, if value is NULL:

$sql = "CREATE TEMPORARY TABLE `nulltest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL DEFAULT '',
  PRIMARY KEY (`id`)
)";

$pdo->exec($sql);

$stm = $pdo->prepare("INSERT INTO nulltest SET name = ?");
foreach (array("foo", null) as $val)
{
    $stm->execute([$val]);
}
$stm->bindParam(1, $val, PDO::PARAM_STR);
$stm->execute();

$stm->bindValue(1, NULL, PDO::PARAM_INT);
$stm->execute();

$sql = "SELECT count(*) FROM nulltest WHERE name IS NULL";
$num = $pdo->query($sql)->fetchColumn();
var_dump($num); // outputs 3

So, the proper code would be

$sql = "INSERT INTO `Group` (visitId, guideId, groupSize, isConfirmed) 
        VALUES (:visitId, :guideId, :groupSize, :isConfirmed)";
$stm = $dbConnection->prepare($sql);
$stm->bindParam(":visitId",     $visitId,     PDO::PARAM_INT); 
$stm->bindParam(":guideId",     $guideId,     PDO::PARAM_INT); 
$stm->bindParam(":groupSize",   $groupSize,   PDO::PARAM_INT); 
$stm->bindParam(":isConfirmed", $isConfirmed, PDO::PARAM_INT); 

foreach($groupId as $void)
{ 
    $stm->execute(); 
}

nulls will be written all right.

edited Nov 4, 2013 at 19:56

answered Oct 21, 2013 at 14:19

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

4 Comments

Bill Karwin Over a year ago

Also one should not count($groupId) on every iteration of the loop. One could count down instead of up: for ($i = count($groupId); $i > 0; $i--). This is not directly related to the question, it's just a minor performance tip.

Your Common Sense Over a year ago

@BillKarwin this one is but another calloused delusion. Nothing wrong with calling count() every time. At least in PHP

Bill Karwin Over a year ago

If the array is small then the impact will be insignificant, but if the array is large it'll be costly. It turns the loop from O(n) to O(n^2). Here's an example of a blogger who did a timing test: electrictoolbox.com/php-for-loop-counting-array

N.B. Over a year ago

It would be interesting to repeat the same test but with up to date version of PHP, not 2009 version.

Subin · Accepted Answer · 2013-10-21 14:28:31Z

1

Change :

$dbHandler->bindParam(":isConfirmed", $isConfirmed, PDO::PARAM_INT);

to :

if(is_null($isConfirmed) || !is_int($isConfirmed)){
 $v=null;
 $dbHandler->bindParam(":isConfirmed", $v, PDO::PARAM_NULL);
}else{
 $dbHandler->bindParam(":isConfirmed", $isConfirmed, PDO::PARAM_INT);
}

edited Oct 21, 2013 at 14:28

answered Oct 21, 2013 at 14:08

Subin

3,5731 gold badge36 silver badges65 bronze badges

3 Comments

Your Common Sense Over a year ago

Better but still flawed

Your Common Sense Over a year ago

single if(is_null($isConfirmed)) will serve better than your horde of conditions

Your Common Sense Over a year ago

But for the 13-year old it is quite a good approach, I have to admit.

Collectives™ on Stack Overflow

MySql and PDO: How to send a null param with bindParam()

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related