CODEWITHSUNDEEP
phpmysql
sveti petar
sveti petar

Reputation: 3797

MySQL order by return of PHP function

I have a MySQL query listing results of restaurants, hotels etc. Each row has latitude and longitude data.

I need to sort the results by distance relative to the user's searched location (I have that latitude and longitude as soon as the page is loaded).

I also have a php function which returns distance in kilometers from two lat/lng pairs:

distance($lat1,$lng1,$lat2,$lng2);

But how do I order the query results by this distance?

Let's say the query is, at the moment, simply

mysql_query("select * from merchants");

Sticking order by distance(latitude,longitude,'$center_lat','$center_long') in there gives an error saying FUNCTION dbname.distance does not exist. I've thought about creating a temporary MySQL tables with the distances as soon as the page is loaded and then sorting by that, but that seems like overkill. Is it?

Upvotes: 0

Views: 197

Answers (2)

DaveRandom
DaveRandom

Reputation: 88697

Could you create a MySQL function that does the same thing as your PHP function? That would be the simplest, most efficient and generally best way, if you are able to do so. If you post the source code of your distance() function I will have a look at converting it to MySQL.

Failing that, you will have to perform the sort in PHP, which is relatively simple when you use array_multisort() (look at Example #3 Sorting database results):

<?php

  // Connect to database, yada yada yada

  // Do the query
  $query = "
    SELECT *
    FROM merchants
  ";
  $result = mysql_query($query);

  // Fetch the results into a 2 dimensional array.
  // Also create an array with the results of the distance function
  $rows = $sortcol = array();
  while ($row = mysql_fetch_assoc($result)) {
    $sortcol[] = $row['distance'] = distance($row['latitude'], $row['longitude'], $center_lat, $center_long);
    $rows[] = $row;
  }

  // Sort the results
  array_multisort($sortcol, SORT_ASC, $rows);

  // Rows should now be sorted
  print_r($rows);

Upvotes: 2

TomTom
TomTom

Reputation: 62129

How do you think your MySql server process calls into your PHP pocess to execute the function?

This wont work - SQL Has not worked like that the last 50 years.

You submit a string to SQL server, it must be evaluatable THERE, not in your host. Your MySql does not know the function, obviously.

Upvotes: 0

Related Questions