Limiting AS distance to two decimal places

[johngordon]

I am just about there getting the Google Maps API talking to my SQL database. For my search results I have the SQL:

SELECT *, ( 3959 * acos( cos( radians(" .$_POST["latitude"] .") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" .$_POST["longitude"] .") ) + sin( radians(" .$_POST["latitude"] .") ) * sin( radians( lat ) ) ) ) AS distance FROM VenuesGeocodes1 HAVING distance < 100 ORDER BY distance LIMIT 0 , 30;

Which works great, but displays the distance with 15 decimal places which is complete overkill.

How can I limit that to just two decimal places? I thought I just needed to change distance to distance (10,2), but it didn’t like it.

If anyone can let me know what it should be that would be much appreciated.

Thanks.

[molona]

Try

SELECT *, TRUNCATE( 3959 * acos( cos( radians(" .$_POST["latitude"] .") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" .$_POST["longitude"] .") ) + sin( radians(" .$_POST["latitude"] .") ) * sin( radians( lat ) ) ) ,2) AS distance FROM VenuesGeocodes1 HAVING distance < 100 ORDER BY distance LIMIT 0 , 30;

or

SELECT *, ROUND( 3959 * acos( cos( radians(" .$_POST["latitude"] .") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" .$_POST["longitude"] .") ) + sin( radians(" .$_POST["latitude"] .") ) * sin( radians( lat ) ) ) ,2) AS distance FROM VenuesGeocodes1 HAVING distance < 100 ORDER BY distance LIMIT 0 , 30;

[chrisofarabia]

I don’t know how to do it at the query level, but depending on how you present it to the browser, PHP has a rounding function, JavaScript may have something similar too.

Edit: Too late, @molona got there first.

[johngordon]

Thank you - it didn’t like TRUNCATE, but worked with ROUND.