0

I have a database like this

https://i.stack.imgur.com/MHEwr.jpg

I have a PHP function which will compute distance { get_distance ($person_location) } of that address from the user (web user).

I need to have a query which will use that function and return the data from the database order by distance from the user [Using { get_distance ($person_location) } function of PHP].

Can anyone help me please?

Abrar Jahin
  • 13,970
  • 24
  • 112
  • 161
  • I am getting User current position from PHP API and product address from DB and get position from PHP API and calculate distance between them with PHP. I am sorting on that distance. Is it possible to do sorting it in MySQL? – Abrar Jahin Nov 02 '14 at 12:15

2 Answers2

2

You can't sort your SQL results on the serverside by the result of a PHP function.*

There are two approaches to your general problem:

1. Move calculation to SQL

Your distance computation probably relies on geo-coordinates (latitude and longitude). Save this data for every address in the database and then do the distance computation in SQL as well.

Find more on how to do this in MySQL here: Fastest Way to Find Distance Between Two Lat/Long Points

Your todo list for this. Do the following things ONCE:

  • Get all your addresses from the DB
  • Calculate the geo coordinates for each address with your PHP API
  • Update your database and put those geo coordinates in extra columns

Do the following things from now on:

  • Every time you add a row to your table, calculate the geo coordinates beforehand with your API and add them as well
  • Every time you change an address in the database, calculate the new geo coordinates with your PHP API and update them as well
  • Every time you need to calculate the distance for the current user to all other addresses, do a SELECT query which computes the distance and does the sorting

2. Do everything in PHP

Query your database for all addresses, put them into a PHP array, compute the distance to the current user with your function and then sort your array.

I strongly suggest not to do that, however, and implement everything on the server-side (Approach 1).

* well in theory you could, by calulcating the distance for every address offline, updating a temporary table with the result, and then querying your table again using this temporary table to sort your results. However, this is even worse than doing everything in PHP, you shouldn't even consider this!

Community
  • 1
  • 1
wolfgangwalther
  • 1,226
  • 7
  • 15
  • I am getting Geo cordinates from PHP API. So, Move calculation to SQL - is a bad option for me. Is there any other solution for it? – Abrar Jahin Nov 02 '14 at 11:54
  • Update your database and add the geo coordinates for every address. This is a one-time-effort. Just make sure, for the future, when adding new rows or changing addresses in the database to update the corresponding geo coordinates as well. Once your geo coordinates are in the database, use the geo coordinates of the current user to find the distance via SQL. See the link in my answer for how to do this. – wolfgangwalther Nov 02 '14 at 11:57
  • And since you explicitly stated FB, google etc in another comment: They are all doing it on their respective database-side... This is the most efficient way. – wolfgangwalther Nov 02 '14 at 12:00
  • It depends on user current position. So, if I use DB for it, it will not satisfy my target... – Abrar Jahin Nov 02 '14 at 12:06
  • Whenever the user changes their position, you have to query the database again. The distance is calculated WHILE RETRIEVING from the database, in the `SELECT` statement. So you are always calculating the **current** distance! You are perfectly fine using the DB for this. – wolfgangwalther Nov 02 '14 at 12:09
  • I am getting User current position from PHP API and product address from DB and get position from PHP API and calculate distance between them with PHP. I am sorting on that distance. Is it possible to do sorting it in MySQL? – Abrar Jahin Nov 02 '14 at 12:15
  • No. I updated my answer with a detailed step by step list (todo) for you. If this is not what you want to or can do, you have to describe your problem a lot more specifically. But that would probably be worth another question. – wolfgangwalther Nov 02 '14 at 12:24
1

imho it is not possible to use PHP functions in your query, only thing like aggregate functions served by MySQL. I guess you need to process through the data by PHP.

hazzard
  • 151
  • 1
  • 1
  • 5
  • Thanks for your kind answer. But if I manipulate thousands of data in PHP, the process will be so slow. So, there must be a faster way to do that in PHP. Because it is used all over in FB, google and many other sites. So, there must be another way. Can anyone please help me? – Abrar Jahin Nov 02 '14 at 11:29