Jump to content

Functions Help!


pacitto513

Recommended Posts

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;	
			}
		}
?>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.