2

I want to have such a loop on the database side instead of the client:

while($result = mysql_fetch_array($exec)) {
    $pitagoras = sqrt(($result['driverpoint_lat']-$lat1)*($result['driverpoint_lat']-$lat1)+($result['driverpoint_lng']-$lng1)*($result['driverpoint_lng']-$lng1));

    if($pitagoras < $distance_least) {
        $distance_least = $pitagoras;
        $closest_driver = $result['iddriver'];
    }
}

Is there any way to replace mysql_fetch_array() if I want to get some data from the row, or maybe whole different way to realize such a function on the mysql side?

Improve this question
7
  • 3
    What's wrong with fetch array? Commented Nov 26, 2012 at 15:33
  • Nothing, it works great but Its database classes and my tutor wants as much as it is possible to be on the database side - he arguments this statement by saying its portability that is given when you make it this way. Commented Nov 26, 2012 at 15:36
  • Use PDO maybe? Commented Nov 26, 2012 at 15:38
  • Don't get confused by the fact that all shown code is PHP. This question has nothing to do with PHP, he's asking about MySQL stored routines. Commented Nov 26, 2012 at 15:39
  • Thanks for the advice but I want it to be mysql stored function/procedure. Commented Nov 26, 2012 at 15:40

3 Answers 3

Reset to default
0

Here is somewhere you can start at. It's not a stored procedure, but I don't see a problem with that:

Build the query so it returns a distance, then order by said distance:

SELECT iddriver, ..., 
(driver_lat - $lat) * (driver_lat - $lat) + 
(driver_lng - $lng) * (driver_lng - $lng) as dxd
FROM ... WHERE ...
ORDER BY dxd ASC

I name that result dxd as in distance times distance because the result is the same and you are not running a SQRT on every row.

The result is a list of drivers with the one at the shortest distance first. You can use a LIMIT on it too.

Note: It's not hard to put this in a prepared statement and/or run it via PDO. Bottom line be mindful of SQL injections.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for that, this is what I didnt think of before but it works great! This is my stored function using your code: 'DELIMITER $$ CREATE FUNCTION sampleFunc9( lat DOUBLE, lng DOUBLE ) RETURNS INT DETERMINISTIC BEGIN DECLARE result DOUBLE; SET result = (SELECT iddriver FROM driver ORDER BY (driverpoint_lat - lat) * (driverpoint_lat - lat) + (driverpoint_lng - lng) * (driverpoint_lng - lng) ASC LIMIT 1); return result; END $$'
0

It seems to me that you want to put this into a function or stored procedure on the database, rather than doing it on the application side. I would not recommend doing this. Databases aren't meant to be doing the work of the applications. Regardless, since your tutor has requested this, take a look at MySQL's mathematical functions.

Looking at the code you have posted, you have variables that are on the application side ($lat1 & $lng1). Would you be passing these to the function?

This answer is a good starting point that shows how mathematical functions are used within a MySQL function and are then called by PHP. Be aware that this is written using mysql_ functions which are being deprecated. You should use mysqli_ or PDO functions.

Improve this answer

3 Comments

I've heard that its better to avoid stored functions/procedures, but as you said I'm bound to use them. Yes, I want the function to be called like "CALL getLeastDistance($lat, $lng)" and return value would be ID of driver that is the closest one to the indicated by lat and long point. I'm gooing to look up the link that you gave, thank you for response.
@DanielSzymatowicz You should try something on your own and then we can help you. Regardless, you need a function, not a stored procedure.
The answer you gave might be the answer as well for me, I will check this out.
0

Thank you @andreshernandez for that, this is what I didnt think of before but it works great! I'm writing here because I couldnt format text with ' '

This is my stored function using your code: 

  DELIMITER $$ 
  CREATE FUNCTION sampleFunc9( lat DOUBLE, lng DOUBLE ) 
  RETURNS INT 
  DETERMINISTIC 
  BEGIN 
  DECLARE result DOUBLE; 
  SET result = (SELECT iddriver FROM driver ORDER BY (driverpoint_lat - lat) * (driverpoint_lat - lat) + (driverpoint_lng - lng) * (driverpoint_lng - lng) ASC LIMIT 1); 
  return result; 
  END $$
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.