pacitto513 Posted October 8, 2013 Share Posted October 8, 2013 Hey everyone, I'm having an issue with attempting to select items from mySQL database using a function that grabs the latitude and longitude of a member and shows how far they live from the user logged in. Every time I run my code I get the following error message: FUNCTION databasename.distance does not exist. I have tried everything I can think of, can someone please help me out on this one! <?php //// GRAB USER LOGGED-IN SEARCH/PERSONAL INFORMATION.////// $uBlatlon = ''; $sql_user_data = mysql_query("SELECT id, lat, lon FROM members WHERE id='$id' LIMIT 1") or die (mysql_error()); while($row = mysql_fetch_array($sql_user_data)) { $userid = $row["id"]; $userlat = $row["lat"]; $userlon = $row["lon"]; } //// distance FUNCTION //// function distance($lat1,$lon1,$lat2,$lon2,$unit) { $theta = $lon1 - $lon2; $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); $dist = acos($dist); $dist = rad2deg($dist); $miles = $dist * 60 * 1.1515; $unit = strtoupper($unit); if ($unit == "K") { return ($miles * 1.609344); } else if ($unit == "N") { return ($miles * 0.8684); } else { return $miles; } } $sql_locals_match = mysql_query("SELECT username lat, lon FROM members WHERE distance(lat,lon,'$userlat','$userlon','K') <= '$distance' ORDER BY last_login DESC") or die (mysql_error()); while($row = mysql_fetch_array($sql_locals_match)) { $memberusername = $row["username"]; $memberlat = $row["lat"]; $memberlon = $row["lon"]; $totaldistance = round(distance($memberlat, $memberlon, $userlat, $userlon,"K")); if ($totaldistance < $distance) { $member_display_list .=''.$memberusername.' - '.$totaldistance.' KM AWAY <br><br>'; }else{ continue; } } ?> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 8, 2013 Share Posted October 8, 2013 your distance(){} function is a php function. it only exists in the context of the php code. you are trying to use it in your sql query statement, which with respect to the php code is just a string you are building. it's only an sql query statement once it has been sent to the database server. to do what you are trying, calculate the distance in the query, so that it will be the fastest and most efficient solution, you would need to define and store a procedure in your database that performs the same calculation using sql statements. Quote Link to comment Share on other sites More sharing options...
pacitto513 Posted October 8, 2013 Author Share Posted October 8, 2013 Hmm okay, thank you for the advice. I;ve never tried making a calculation witihn a query - gonna be tricky. Quote Link to comment Share on other sites More sharing options...
pacitto513 Posted October 8, 2013 Author Share Posted October 8, 2013 Would someone be willing to help me out with this one, with respect to the code that I have already provided?? Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 8, 2013 Share Posted October 8, 2013 Either do what mac_gyver suggested, by converting your php distance function to a stored procedure in SQL. Or grab all the results from the database and run each user's latitude and longitude through your distance function separately. You cannot call PHP functions within SQL queries. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.