How to use addSelectExpr ?
4 posts by 2 authors in: Forums > CMS Builder
Last Post: December 23, 2009 (RSS)
By Djulia - December 21, 2009 - edited: December 21, 2009
I would like to understand the use of the new option addSelectExpr.
For example, if I use this code to obtain the recordings in a radius :
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC
...It is possible to simplify the use with addSelectExpr and cmsB like this :
list($geoRecords, $geoMetaData) = getRecords(array(
tableName' => 'geo',
'limit' => '5',
'orderBy' => 'distance ASC',
'addSelectExpr' => ' ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance HAVING distance<='10' ',
));
Thanks,
Djulia
http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html (Thanks Dave !)
Re: [Djulia] How to use addSelectExpr ?
By Dave - December 22, 2009
Are you using that code and wanted to get to work? Or are you just curious how to use addSelectExpr?
interactivetools.com
Re: [Dave] How to use addSelectExpr ?
By Djulia - December 22, 2009 - edited: December 22, 2009
I use a similar request SQL on a real estate site and cmsB. But, my approach limits the possibilities (geo____.txt).
Also, it would be perfect if I could use my request SQL with a page of the list type and the options available (Show perPage [font "Times New Roman"]prev & next, Show file uploads, search…).
Does [font "Times New Roman"]addSelectExpr give this possibility ?
Thank you for your answer.
Djulia
Re: [Djulia] How to use addSelectExpr ?
By Dave - December 23, 2009
Yes I think it will work for you, but I'll need to add a "having" option since you are using that to. If you can email me CMS and FTP login details and an url to a working test page I can show you how to do it and add "having".
We were planning on adding a "having" option but wanted to wait until we had a real-world example to test it on.
I should be able to get this done in the next week (slower because of the holiday).
Remember to email (don't post login details to the forum).
Hope that helps! :)
interactivetools.com