MYSQL Query Help

[bravo14]

Hi

 

I have two tables tbl_users this holds all of the user data including latitude and longitude, and tbl_auction_lot this holds details of items available for auction.

 

i am trying to write a query that displays all of the auction lots within a certain distance

 

This will display the distance, however as soon as I put in * from both tabkes I get syntax errors

 

SELECT ((ACOS(SIN(52.4564772 * PI() / 180) * SIN(latitude * PI() / 180) + COS(52.4564772 * PI() / 180) * COS(latitude * PI() / 180) * COS((-1.7675127 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `tbl_users` INNER JOIN `tbl_auction_lot` on `tbl_auction_lot`.`cust_id`=`tbl_users`.`cust_id` HAVING `distance`<='10' ORDER BY `distance` ASC

 

How can I get other fields displayed, or is it case of listing the fields that I require, rather than * fields

[requinix]

Qualify the * with the table name, like `tbl_auction_lot`.*. But you should try to include only the fields you actually need.

[fenway]

Also, that just wastes CPU cycles -- you don't need all those trig functions unless you're using huge distances.

[bravo14]

Also, that just wastes CPU cycles -- you don't need all those trig functions unless you're using huge distances.

 

Is there an easier way of doing it? I found this query on a tutorial

[Barand]

found this via google

 

If your displacements aren't too great (less than a few kilometers) and you're not right at the poles, use the quick and dirty estimate that 111,111 meters in the y direction is 1 degree (of latitude) and 111,111 * cos(latitude) meters in the x direction is 1 degree (of longitude).

 

[fenway]

Pythagoras works wells.