PDO::bindParam with GeomFromText

So I heard you're using PDO, so you should use bindParam to replace your parameters in your requests DQL.

Now, suppose you use functions in your requests, such as GeomFromText :

$query = "
	SELECT * FROM points p
	WHERE p.point = GeomFromText('POINT(:lat :lng)')";

$statement = $db->prepare($query);

Then, you'd do something like that :

$statement->bindParam(':lat', 2.3, PDO::PARAM_STR);
$statement->bindParam(':lng', 5.7, PDO::PARAM_STR);

With that you should normally issue a request along these lines :

SELECT * FROM points p WHERE p.point = GeomFromText('POINT(2.3 5.7)')

But unfortunately this would'nt work ! :lat and :lng are not replaced because our good friend PDO doesn't replace parameters inside strings, and 'POINT( ...)' is indeed a string that will be interpreted directly by the request engine.

Obviously :

$query = "
	SELECT * FROM points p
	WHERE p.point = GeomFromText(POINT(:lat :lng))";

.. doesn't work either because 'POINT( ...)' is not really a function and we definitely need the quotes here.

So how do we do ?

Solution : you have to prepare your string beforehand :

$query = "
	SELECT * FROM points p
	WHERE p.point = GeomFromText(:point)";

$statement = $db->prepare($query);
$point = sprintf("POINT(%F %F)", 2.3, 5.7);
$statement->bindParam(':point', $point, PDO::PARAM_STR);

Here, sprintf protects us from a possible injection problem, and bindParam escapes everything well.

That's all folks !


Some useful links here and here