1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to use variable in a WHERE statement

Discussion in 'MySQL' started by Peterbrk57, Apr 19, 2020.

  1. #1
    Hi,

    I have in a table a column Latitide and a column Longitude. These are coordinates from a store.
    With geolocation I got the coordinates (Latitude and Longitude) from a user of my website.
    With a formula I can calculate the distance between the coordinates.

    I have the distance inside the WHILE loop.

    What I want is to select only the records where the distance is less then 10km.

    But how? So far I know I cannot use a variable in the WHERE statement and before I can select records with WHERE I have to calculate the distance.

    Can somebody help me? Is this enough information, sorry for my English, I am Dutch.
     
    Peterbrk57, Apr 19, 2020 IP
  2. richto99

    richto99 Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    3
    #2
    select * from Gtable where (Latitude, Longitude)
    in (select Latitude, Longitude Gtable where distance(Latitude, Longitude,x,y)<10km)

    x,y = coordinates (Latitude and Longitude) from a user of my website
     
    richto99, Apr 19, 2020 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #3
    Distance needs some funky calculations because the earth isn't flat (no, really!) so you need to look those up. I can't even remember what it's called but I'm assuming it's the formula you're referring to.

    The smartest way would be to write a function in MySQL and reference that in your fields. Something like this:
    //$lat & $long are sourced from the users form, their profile or however you get it
    
    $sql = "select store_id, myfunc({$lat},{$long}) as distance
    from `store_locations`
    order by distance limit 10";
    Code (markup):
    There will be an overhead of running the function every time but if you only have 100 stores it won't be too bad. If it's huge then you should write something to generate a rough estimate of what the max and min latitude and longitude are so that it looks more like this:

    $sql = "select store_id, myfunc({$lat},{$long}) as distance
    from `store_locations`
    where store_locations.lat between ({$minLat},{$maxLat})
    and store_locations.long between({$minLong},{$maxLong}
    order by distance limit 10";
    Code (markup):
     
    sarahk, Apr 19, 2020 IP
  4. Peterbrk57

    Peterbrk57 Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #5
    Thank you both, after a afternoon playing with numbers I did this:

    $radius = 30;

    $lat_min = $lat - 0.014605113 * $radius;
    $lat_max = $lat + 0.014605113 * $radius;
    $long_min = $long - 0.008991805 * $radius;
    $long_max = $long + 0.008991805 * $radius;

    FROM deseo_shopping_reclame r,
    migusta_periodes p,
    deseo_shopping_naw k
    $samen r.id = p.id_reclame AND r.klantnummer = k.klantnummer AND r.id <> $except_id AND p.einddatum_reclame > $nu AND p.begindatum_reclame < $nu
    AND k.longitude < $long_max AND k.longitude > $long_min AND k.latitude < $lat_max AND k.latitude > $lat_min

    It is not 100% but good enough for what I want and it works!
     
    Peterbrk57, Apr 20, 2020 IP