Thursday, June 16, 2011

PHP PDO: so hard to debug

I wrote a simple PDO helper function to update fields in a certain database table. The fields are given as the key/value pairs in $d, and my function looked like this:
$q='UPDATE MyTable SET lastupdate=:lastupdate';
foreach($d as $key=>$value)$q.=', '.$key.'=:'.$key;
$q.=' WHERE username=:username';

$statement=$dbh->prepare($q);
foreach($d as $key=>$value)$statement->bindParam(':'.$key,$value);
$statement->bindParam(':lastupdate',date('Y-m-d H:i:s'));
$statement->bindParam(':username',$username);
It all looks reasonable doesn't it? Create the SQL, then assign the values to. But it didn't work. My $d array looked like:
array( 'status'=>'expired', 'mode'=>'' )
Instead of getting set to expired, the status field ended up blanked out. Yet lastupdate and username got set. This had me scratching my head for ages.
PDO has a debug function that is next to useless: it tells you the parameters, but not the values you've assigned to them. Incredibly annoying.

Have you spotted my bug yet?

Here's the answer. Though all the examples in the PHP documentation use bindParam(), the function to assign a value is bindValue(). You should always use bindValue(), unless you actually need the advanced functionality that bindParam() gives you. What advanced functionality you wonder? Instead of assigning the value immediately, it attaches a reference, and uses the final value of that reference variable. You're ahead of me: in my foreach loop the $value variable changes on each iteration.

If PDO had a decent debug function I'd have discovered that in half the time. Oh well, now I know!

No comments: