CODEWITHSUNDEEP
phpmysqldatabase
Hopewell Mutanda
Hopewell Mutanda

Reputation: 1075

Calling a function in a php select statement

I am trying to order my results according to distances in ascending order. I there created php function to calculate the distances and return the value. When i test the function in isolation it works, but when i try to call it in a php select statement it does not work. Where might i be going wrong. Below is the code snippet.

<?php
include 'database.php';
$pdo = Database::connect();

function distance($lat1, $lon1, $lat2, $lon2) {
  $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;
  return ($miles * 1.609344); 
}

$sql = 'SELECT weather_locations.location_id, weather_locations.location_name,
distance(weather_locations.latitude, weather_locations.longtitude, -20.132507, 28.626479) AS mydistance,
...
...
ORDER BY mydistance'

Those hard coded values are just to test. Otherwise i will retrieve the real values from the user

Upvotes: 1

Views: 84

Answers (2)

cb0
cb0

Reputation: 8613

Store the distance first and then insert it to the query.

$distance = distance($weather_locations.latitude, $weather_locations.longtitude, -20.132507, 28.626479);
$sql = 'SELECT weather_locations.location_id, weather_locations.location_name, '.
$distance.' AS mydistance, ...'

Look here on how to cancat string in php.

Update

Based on your comment I see you misunderstand some things. You need to first get the values and then query the db.

    $latitude_query = "SELECT latitude ..."
    $longtitude_query = "SELECT longtitude ..."
    $latitude = $latitude_query->fetch();
    $longtitude = $longtitude_query->fetch();
    $distance = distance($latitude, $longtitude, -20.132507, 28.626479);

    $sql = 'SELECT weather_locations.location_id, weather_locations.location_name,
    '.$distance.' AS mydistance, ...'

Upvotes: 0

Pratik Joshi
Pratik Joshi

Reputation: 11693

Use proper concatenation

$sql = 'SELECT weather_locations.location_id, weather_locations.location_name,'.distance(weather_locations.latitude, weather_locations.longtitude, -20.132507, 28.626479).' AS mydistance ...

Explanation :

in your Code , your function is considered as Plain string.So you need to tell php that hey PHP, this is function, not a string. So to separate function from rest of the string make 3 parts of query here.

  1. String part 1 (before function).

  2. returned value (the function).

  3. String part 3 (after function).

And concate them all.

Edit 2 : More explanation:

part 1 : 

'SELECT weather_locations.location_id, weather_locations.location_name,'

part 2 :

 distance(weather_locations.latitude, weather_locations.longtitude, -20.132507, 28.626479)

part 3 :

' AS mydistance, ...'

Your code has error so your final query would come up like(which was your wrong code) : 

'SELECT weather_locations.location_id,weather_locations.location_name,distance() AS mydistance ...'

Where do you see result of your function ?? It is treated as string , so function will not be executed in your code.

So My final Proper Query built will result like this -> 

'SELECT weather_locations.location_id,weather_locations.location_name,Your result Of Function named as Distance() AS mydistance ...'

Hope it makes sense.

Upvotes: 1

Related Questions