I'm developing an application using CakePHP 1.3. I have a page which allows users to search through 'Outlet' table records.
I have three fields in the search form; name, cuisine and postcode. This is what the view for search looks like; http://pastebin.com/vadS4gcb.
I have set up validation for only the postcode field, it can be left empty but if a value is entered CakePHP's built in postcode validation rule is applied. This is what the search action looks like in my Outlets controller; http://pastebin.com/jiPUehkv.
If a user enters a valid postcode in the search field then matching records from the Outlets table are meant to be retrieved and displayed on the search results page along with how far (in miles) they are from the postcode entered by the user. I have used CakeDC's search plugin to implement the search functionality, http://cakedc.com/downloads/view/cakephp_search_plugin.
Here is what my Outlet model class looks like; http://pastebin.com/yJ1HykTT.
The search functionality almost works as exepected however I noticed that when searching only by distance (ie entering a postcode in the search field and leaving the other two fields blank) Outlets that are less than one mile away from the given postcode are not retrieved from the DB. I can't figure out why this is.
If I enter data in the search form for any of the other two fields (name and cuisine) AND a valid postcode than all matching records including those which are less than one mile away are retrieved. I can't figure out why this isn't happening in the previous case.
Here are the queries when only postccode is entered; http://pastebin.com/37A0SP7G
Here are the queries when a postcode is provided and text is entered in the name field; http://pastebin.com/a2mR7Yi0.
I formed my Haversine query by following Google's guide; http://code.google.com/intl/hu-HU/apis/maps/articles/phpsqlsearch.html which gave me:
'( 3959 * acos( cos( radians("'.$latLong['latitude'].'") ) * cos( radians( Outlet.latitude ) ) * cos( radians( Outlet.longitude ) - radians("'.$latLong['longitude'].'") ) + sin( radians("'.$latLong['latitude'].'") ) * sin( radians( Outlet.latitude ) ) ) )';