0

I have a fully working user defined function called CalculateDistance which will calculate the distance between the provided postcode and the one within the function.

I want to know if there is some way to use this function to create some extra data. I want a query that will fetch all of the data from the database, as well as provide this distance calculation.

In simple terms, I have a database full of rental properties and the attributes are: Name, Description and Postcode. I have other attributes which I have stripped away. I want to be able to call a query in PHP like this:

$query = mysqli_query($con,"SELECT * FROM Properties WITH DistanceTo = CalculateDistance(Postcode) ORDER BY ID ASC")){;
while($PropertyInfo = mysqli_fetch_array($query)){
echo $PropertyInfo['Name'];
    echo $PropertyInfo['DistanceTo'];
}
user2879284
  • 1
  • 2

1 Answers1

0

To show the distance and filter by the distance your query should be something like 

SELECT p.*, CalculateDistance(p.Postcode) [Distance]
FROM Properties p
WHERE p.DistanceTo = CalculateDistance(p.Postcode) 
ORDER BY ID ASC
user2989408
  • 3,127
  • 1
  • 17
  • 15