note 96264 added to book.pdo
| From: | jose at thezcompany dot com | Date: | Wed, 17 Feb 2010 20:39:09 +0000 |
| Subject: | note 96264 added to book.pdo | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-166090@lists.php.net to get a copy of this message | ||
I ran into a real annoying bug/feature when using PDO for SQL statements that use SQL user
variables. I was working on some logic for a Geo Proximity Search for an events-venues system
(sharing is caring so it's below) and it just wouldn't take and the errors returned were
garbage. The SQL was sound as I verified it. So if you're having this issue, I hope this
helps. What you need to do is break apart the query into two...
From:
<?php
$sql="set @latitude=:lat;
set @longitude=:lon;
set @radius=20;
set @lng_min = @longitude - @radius/abs(cos(radians(@latitude))*69);
set @lng_max = @longitude + @radius/abs(cos(radians(@latitude))*69);
set @lat_min = @latitude - (@radius/69);
set @lat_max = @latitude + (@radius/69);
SELECT *,
3956 * 2 * ASIN(SQRT(POWER(SIN((@latitude - ABS(venue_lat)) * PI()/180 / 2),2) + COS(@latitude *
PI()/180) * COS(ABS(venue_lat) * PI()/180) * POWER(SIN((@longitude - venue_lon) * PI()/180 / 2),2)))
AS distance
FROM events LEFT JOIN venues ON venues.venue_id = events.venue_fk
WHERE (venue_lon BETWEEN @lng_min AND @lng_max)
AND (venue_lat BETWEEN @lat_min and @lat_max)
AND events.event_date >= CURDATE()
AND events.event_time >= CURTIME()
ORDER BY distance DESC;";
$stmt = $this->_db->prepare($sql);
$stmt->bindParam(':lat', $lat, PDO::PARAM_STR);
$stmt->bindParam(':lon', $lon, PDO::PARAM_STR);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':max', $max, PDO::PARAM_INT);
$stmt->execute();
?>
To:
<?php
$sql = "SET @latitude=:lat;
SET @longitude=:lon;
SET @radius=20;
SET @lng_min=@longitude - @radius/abs(cos(radians(@latitude))*69);
SET @lng_max=@longitude + @radius/abs(cos(radians(@latitude))*69);
SET @lat_min=@latitude - (@radius/69);
SET @lat_max=@latitude + (@radius/69);";
$this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $this->_db->prepare($sql);
$stmt->bindParam(':lat', $lat, PDO::PARAM_STR);
$stmt->bindParam(':lon', $lon, PDO::PARAM_STR);
$stmt->execute();
$sql = "SELECT *,
(3956 * 2 * ASIN(SQRT(POWER(SIN((@latitude - ABS(venue_lat)) * PI()/180 / 2),2) + COS(@latitude *
PI()/180) * COS(ABS(venue_lat) * PI()/180) * POWER(SIN((@longitude - venue_lon) * PI()/180 /
2),2)))) AS distance
FROM events LEFT JOIN venues ON venues.venue_id = events.venue_fk
WHERE (venue_lon BETWEEN @lng_min AND @lng_max)
AND (venue_lat BETWEEN @lat_min and @lat_max)
AND events.event_date >= CURDATE()
AND events.event_time >= CURTIME()
ORDER BY distance DESC
LIMIT :offset,:max;";
$stmt = $this->_db->prepare($sql);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':max', $max, PDO::PARAM_INT);
$stmt->execute();
?>
Hope this helps anyone out there!
----
Server IP: 194.236.124.41
Probable Submitter: 98.64.216.198
----
Manual Page -- http://www.php.net/manual/en/book.pdo.php
Edit -- https://master.php.net/note/edit/96264
Del: integrated -- https://master.php.net/note/delete/96264/integrated
Del: useless -- https://master.php.net/note/delete/96264/useless
Del: bad code -- https://master.php.net/note/delete/96264/bad+code
Del: spam -- https://master.php.net/note/delete/96264/spam
Del: non-english -- https://master.php.net/note/delete/96264/non-english
Del: in docs -- https://master.php.net/note/delete/96264/in+docs
Del: other reasons-- https://master.php.net/note/delete/96264
Reject -- https://master.php.net/note/reject/96264
Search -- https://master.php.net/manage/user-notes.php