CODEWITHSUNDEEP
phpmysqlsqlweblaravel-4
S M Abrar Jahin
S M Abrar Jahin

Reputation: 14588

Custom MySQL Order By depending on a PHP function value

I have a database like this

https://i.sstatic.net/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?

Upvotes: 0

Views: 75

Answers (2)

wolfgangwalther
wolfgangwalther

Reputation: 1236

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!

Upvotes: 2

hazzard
hazzard

Reputation: 136

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.

Upvotes: 1

Related Questions