CODEWITHSUNDEEP
mysqlbetween
Sebastian
Sebastian

Reputation: 3628

What's wrong with my MySQL query? (using BETWEEN)

I'm trying to select businesses from my database who fall within a 10mi radius of the user. Testing shows that the problem lies in my query (though no MySQL error is thrown). The variables $lat and $lng are imported from Javascript and they, too, are fine. I suspect the issue is something to do with my use of BETWEEN, as this is the first time I've used it. Any ideas?

$r = 20; // in miles
$lat_min = $lat - ($r / 69);
$lat_max = $lat + ($r / 69);
$lng_min = $lng - $r / abs(cos(deg2rad($lat)) * 69);
$lng_max = $lng + $r / abs(cos(deg2rad($lat)) * 69);

$query = "SELECT name FROM businesses WHERE $lat BETWEEN $lat_min AND $lat_max $lng BETWEEN $lng_min AND $lng_max ORDER BY Rand() LIMIT 6";

Upvotes: 0

Views: 305

Answers (6)

triclosan
triclosan

Reputation: 5714

$query = "SELECT name FROM businesses WHERE $lat BETWEEN $lat_min AND $lat_max AND $lng BETWEEN $lng_min AND $lng_max ORDER BY Rand() LIMIT 6";

Upvotes: 1

BMN
BMN

Reputation: 8508

Like, the other answers, and to be sure, place ( ).

$query = 'SELECT name 
FROM businesses 
WHERE 
('.$lat.' BETWEEN "'.$lat_min.'" AND "'.$lat_max.'")
AND ('.$lng.' BETWEEN "'.$lng_min.'" AND "'.$lng_max.'")
ORDER BY Rand() LIMIT 6";

Upvotes: 1

Josh
Josh

Reputation: 2975

I think you need an AND in the WHERE clause...

WHERE ($lat BETWEEN $lat_min AND $lat_max) AND ($lng BETWEEN $lng_min AND $lng_max)

Upvotes: 0

MikeTheReader
MikeTheReader

Reputation: 4190

Assuming $lat and $lng resolve to actual column names, I think you're just missing an "AND". Sometimes, just formatting the SQL statement can reveal the issue.

SELECT name 
  FROM businesses 
  WHERE $lat BETWEEN $lat_min AND $lat_max 
    **AND** $lng BETWEEN $lng_min AND $lng_max 
ORDER BY Rand() LIMIT 6;

Upvotes: 0

George Johnston
George Johnston

Reputation: 32258

Looks like you have you're missing and AND e.g.

SELECT 
   name
FROM
   businesses
WHERE
  $lat BETWEEN $lat_min AND $lat_max
  AND $lng BETWEEN $lng_min AND $lng_max
ORDER BY Rand() LIMIT 6

Upvotes: 1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

The query should be:

  SELECT name FROM businesses 
  WHERE THE_ACTUAL_NAME_OF_THE_COLUMN BETWEEN $lat_min AND $lat_max 
  AND THE_NAME_OF_THE_NEXT_COLUMN BETWEEN $lng_min AND $lng_max 
  ORDER BY Rand() LIMIT 6

Somehow $lat does not look like a column name in the table. Also you were missing an AND.

Upvotes: 8

Related Questions