Functions Help!

[pacitto513]

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;	
			}
		}
?>

[mac_gyver]

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.

[pacitto513]

Hmm okay, thank you for the advice. I;ve never tried making a calculation witihn a query - gonna be tricky.

[pacitto513]

Would someone be willing to help me out with this one, with respect to the code that I have already provided??

[Ch0cu3r]

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.