sorting distance in MySQL PHP [duplicate]

2019-09-20 06:34发布

问题:

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

My Code:

<?php 
$Sql="SELECT *, (3959 * acos(cos(radians(37)) * cos(radians(44)) * cos(radians(55) -    radians(-122)) + sin(radians(37)) * sin(radians(44))) as distance FROM TableName       HAVING distance < 25 ORDER BY distance LIMIT 0 , 20";
$result=mysql_query($Sql); 

while ($row = mysql_fetch_array($result)){
   echo $row['Id'];
}

Error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home...

Hi, I have longitude and latitude stored in the database and I want to calculate the KM - distance based on the user's current longitude and latitude with the shop's longitude and latitude stored in database. And I want a sorted result as well based on the distance. I googled and found some solutions but getting weird error.. Please check my code. I am not using variable in query. I just want to test if it runs. Please help

回答1:

I found new it should be like this

(3959 * acos(cos(radians(37)) * cos(radians(44)) * cos(radians(55) -    radians(-122)) + sin(radians(37)) * sin(radians(44))))

You are missing ")"



回答2:

No of opening parentheses is not equal to no of closing parentheses in your SQL statement.

Try this...

SELECT *, (
        3959 * acos(
                 cos(radians(37)) * cos(radians(44)) * 
                 cos(radians(55) -  radians(-122)) + 
                 sin(radians(37)) * sin(radians(44))
                )
       )
    as distance FROM TableName HAVING distance < 25 ORDER BY distance LIMIT 0 , 20


回答3:

If you are storing points in your database, you might consider using: http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html if your MySQL version is 5.0.16 or later.

An example here: http://howto-use-mysql-spatial-ext.blogspot.com/