Subquery calculation error?

Question

Why is this query not working?

$query = ("SELECT * FROM 
                (SELECT *, (
                (((endingLatitude - " . $h . ")(endingLatitude - " . $h . "))
                /" . pow($r1, 2) . ")
                 + 
                 ((endingLongitude - " . $k . ")(endingLongitude - " . $k . "))
                 /" . pow($r2, 2) . "
                 ) AS point2 
                FROM (SELECT *, ((
                (
                ((startingLatitude - " . $h . ")(startingLatitude - " . $h . "))
                /" . pow($r1, 2) . ") 
                + 
                ((startingLongitude - " . $k . ")(startingLongitude - " . $k . "))
                /" . pow($r2, 2) . ")) 
                AS point1 
                FROM (SELECT * FROM trips WHERE distance >='" . ($distance * .25) . "') as query1)
                 as query2) as query3 WHERE point1 <= 1 AND point2 <= 1 LIMIT 0 , 10;");

$result = mysqli_query($con, $query);

$h and $k is the ellipses x and y coordinates respectively. I am using a formula found here to calculate whether or not the two points, (startingLat,startingLong) and (endingLat,endingLong) are within an ellipse with vertical height $r1 and horizontal height $r2. I am also limiting the rows that I search to rows that have a distance cell value of greater than $distance * .25.

I think it might have something to do with a parenthesis error or something to do with the way I am sub querying/performing my calculations.

Using

die(mysqli_error($con));

returns an error of You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(endingLatitude - 36.9564753)) /796.842964388) + ((endingLongitud' at line 3

@anantkumarsingh it fails `if(!$result)` then prints, `$result = mysqli_query($con, $query);` — Henry Harris, May 29 '15 at 04:52
put `$result = mysqli_query($con, $query) or die(mysqli_error($con));` and check the real error you are getting. thanks. — Alive to die - Anant, May 29 '15 at 04:54
@anantkumarsingh sorry about that, `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(endingLatitude - 36.9564753)) /796.842964388) + ((endingLongitud' at line 3` — Henry Harris, May 29 '15 at 04:55
@anantkumarsingh done, any ideas of what the source of this error is? — Henry Harris, May 29 '15 at 05:00
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/79082/discussion-between-anant-kumar-singh-and-henry-harris). — Alive to die - Anant, May 29 '15 at 05:05

score 2 · Accepted Answer · answered May 29 '15 at 05:01

2

I believe you have to use Mysql multiplication arithmetic operator, *. https://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html#operator_times

Instead of:

(endingLatitude - " . $h . ")(endingLatitude - " . $h . ")

Do this...

(endingLatitude - " . $h . ") * (endingLatitude - " . $h . ")

answered May 29 '15 at 05:01

Filip

1,214
10
19

This could be it! Fixes the error, however, my query still does not function properly so it must be something else... I'll look into it. – Henry Harris May 29 '15 at 05:07

Subquery calculation error?

1 Answers1