0

Im having real trouble converting a SQL query from mysql to a PDO prepared statement. It is the following and it is used to calculate the distance between a coordinate stored in a session (the users position) and a coordinate in the database, and if it is within a radius determined by the user:

$query = "
SELECT 
* 
FROM 
first_page_data 
WHERE 
((((acos(sin((".$_SESSION['alat']."*pi()/180)) * 
sin(('geo_lat1'*pi()/180))+cos((".$_SESSION['alat']."*pi()/180)) * 
cos(('geo_lat1'*pi()/180)) * cos(((".$_SESSION['alon']."- 'geo_lon1') * 
pi()/180))))*180/pi())*60*1.1515) * (1.609344/1000)) < ".$_SESSION['aradius'];

I want to change the session variables, and the geo_lat and geo_lon variables into placeholders so I can bind the values to them but I cant for the life of me get it to work!

I dont even know how to see how im getting on because once ive replaced the variables above with placeholders (ive been using the unnamed questionmark placeholders) and then binded the values to them, I dont know how to retrieve the compiled query from $stmt before I execute it:

$stmt = db->prepare($query);
$stmt->bindValue(1, $_SESSION['alat'], PDO::PARAM_STR);
**more bindings**
$stmt->execute();
Giovanni
  • 850
  • 3
  • 14
  • 32
  • Please show us the exact code that you've tried, and then tell us what happens when you try it. – Andy Lester Apr 02 '14 at 20:28
  • Why do you want to retrieve the compiled query before executing the query? – Sean Apr 02 '14 at 20:29
  • Then make the changes. I recommend using *names* for placeholders. With placeholders, you *don't* get to see the "query with values". Hooray! – user2864740 Apr 02 '14 at 20:30

1 Answers1

2

These changes are very trivial. I put in placeholder names, removed the string concatenation, and removed the incorrect quotes. It could use help with formatting, or perhaps a UDF.

# Change string-concat for placeholders
$query = "
SELECT 
* 
FROM 
first_page_data 
WHERE 
((((acos(sin(( :lat1 *pi()/180)) * 
sin((geo_lat1*pi()/180))+cos(( :lat2 *pi()/180)) * 
cos((geo_lat1*pi()/180)) * cos((( :lon - geo_lon1) * 
pi()/180))))*180/pi())*60*1.1515) * (1.609344/1000)) < :rad";

# Bind values (PARAM_STR is default)
# While PDO does support using a named parameter multiple times
# when emulating placeholders, this is not guaranteed. To be safe,
# here the code is binding the same value to two "different" params.
$stmt = db->prepare($query);    
$stmt->bindValue(":lat1", $_SESSION['alat']);
$stmt->bindValue(":lat2", $_SESSION['alat']);
$stmt->bindValue(":lon", $_SESSION['along']);
$stmt->bindValue(":rad", $_SESSION['aradius']);

# Execute! I recommend enabling PDO Exceptions to avoid so much
# manual error-checking of result values.
$stmt->execute();

Now, there is no direct way to see the statement with data as that's not how placeholders work - even if PDO internally emulates placeholders it's merely an implementation detail and you don't get to see it.

However, there are various solutions to the more general task of "debugging statements" discussed in How to debug PDO database queries? (I recommend using the database logging for development as your code probably shouldn't be logging or printing queries directly.)

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
  • It was the string concatenation that was throwing me off! Thanks for the advice too - v.helpful :) – Giovanni Apr 02 '14 at 20:53
  • 1
    @Giovanni You're welcome! Hooray for switching to placeholders - your code is now tidier and safer :) – user2864740 Apr 02 '14 at 20:58
  • ps is that a known problem in PDO that the same placeholder cant be used in different places? The reason being I actually have geo_lat2 and geo_lat3 to add to this query in an 'OR' so when I add these I will end up with 6 placeholders all referring to $_SESSION['alat'] !! – Giovanni Apr 02 '14 at 21:37
  • @Giovanni *If* PDO is using [ATTR_EMULATE_PREPARES](http://www.php.net/manual/en/pdo.setattribute.php) then it will allow duplicating with placeholder within the query as PDO itself generates the final SQL, and it handles this case. See [PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?](http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not) for some additional considerations. – user2864740 Apr 02 '14 at 21:41
  • @Giovanni Maybe there is a way that the query could itself be simplified or use a UDF/Function? If putting the SELECT *inside* a function, then when called, the parameters to the function would only be bound once, but could be used multiple times from within the function. Likewise, a UDF could be invoked directly in the condition and replace the long math formula. – user2864740 Apr 02 '14 at 21:44
  • Im sorry I dont understand what you mean by 'use a UDF/Function'? Also I have always set ATTR_EMULATE_PREPARES to false, as when I was learning PDO in the beginning i was told it was only really usable in older versions of MySQL – Giovanni Apr 02 '14 at 21:52
  • @Giovanni See [CREATE FUNCTION](https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html). – user2864740 Apr 02 '14 at 23:17