PDO and PDO::PARAM_INT
Hi there Mr. PDO!
We’ve come to know each other, and yes, while you have your troubles (.. which I don’t, of course), I’ve accepted your short comings. Today you threw another one of your fits, but I’ll be sure to document it for the world to see.
-
$statement = $pdo->prepare("
-
…
-
LIMIT
-
:offset, :hits
-
");
Yep. This will of course fail if you’re binding strings. ’10′, ’10′ is not very helpful now, is it. Good point. So let’s tell PDO that we’re really binding ints:
-
$statement->bindValue(':offset', $offset, PDO::PARAM_INT);
-
$statement->bindValue(':hits', $hits, PDO::PARAM_INT);
But wait. You’re still complaining?! I told you they were ints?! What’s the problem now?!?!
Well. Mr. PDO requires you to also convert the values for him. So first you have to convert the values of a loosely typed language to a strong type, then you have to tell the library that yes, this is in fact another type than what the library obviously assumes that it is. This works:
-
$statement->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
-
$statement->bindValue(':hits', (int) $hits, PDO::PARAM_INT);
Which means the following:
If the type of your variable internally is a string, it’ll be escaped as a string, even if you tell PDO that it should be handled as an INT in your database layer.
If the type of your variable is an int, it’ll be handled as a string, unless you tell PDO it is an int.
Something is backwards here.
November 26th, 2009 at 21:24
Thank you very much, this helped me!
August 12th, 2010 at 21:24
[...] This is explained more in detail here: http://e-mats.org/2009/02/pdo-and-pdoparam_int/ [...]