SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Store Locator - Not showing all results?!?

    I have 2 stores in my database both with LAT & LON generated from Google Maps.

    The postcodes are CM18 6QQ (51.75792090,0.10526110) and CM17 0PB (51.78607040,0.17861950)

    I use this query for the search function in MySQL
    Code:
    SELECT *, (3959 * acos( cos( radians($LAT) ) * cos( radians( shopLat ) ) * cos( radians( shopLon ) - radians($LON) ) + sin( radians($LAT) ) * sin( radians( shopLat ) ) ) ) AS distance
    FROM shopList
    HAVING distance < 25
    ORDER BY distance
    LIMIT 0,10
    When you enter a postcode into the search box, it checks the database to see if that has already been searched for and uses the LAT/LON already saved, otherwise it gets the LAT/LON for that postcode.

    If I do a search for CM18 6QQ it returns both rows in the results correctly, however, if I do a search for CM17 0PB, it only returns the CM18 6QQ row in the result.

    Has me quite confused why this would be.

    This is the MySQL query that's not returning correctly:
    Code:
    SELECT *, (3959 * acos( cos( radians(51.78607040) ) * cos( radians( shopLat ) ) * cos( radians( shopLon ) - radians(0.17861950) ) + sin( radians(51.78607040) ) * sin( radians( shopLat ) ) ) ) AS distance
    FROM shopList
    HAVING distance < 25
    ORDER BY distance
    LIMIT 0,10
    but this one is fine:
    Code:
    SELECT *, (3959 * acos( cos( radians(51.75792090) ) * cos( radians( shopLat ) ) * cos( radians( shopLon ) - radians(0.10526110) ) + sin( radians(51.75792090) ) * sin( radians( shopLat ) ) ) ) AS distance
    FROM shopList
    HAVING distance < 25
    ORDER BY distance
    LIMIT 0,10

  2. #2
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I have figured it out...

    If I remove the HAVING distance < 25 then it returns all the results - sometimes the results have NULL as a distance and other times 0.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •