MySQL order by return of PHP function

Question

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?

DaveRandom · Accepted Answer · 2012-05-17 09:26:24Z

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);

TomTom · Accepted Answer · 2012-05-17 09:18:26Z

0

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.

answered May 17, 2012 at 9:18

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

2 Comments

sveti petar Over a year ago

Yes, I realize that now. But what is the solution?

deceze Over a year ago

@robert Port the sorting algorithm to MySQL. The Great Circle Distance Formula for SQL is a well covered topic.

Collectives™ on Stack Overflow

MySQL order by return of PHP function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related