CODEWITHSUNDEEP
mysql
vman
vman

Reputation: 321

Between mysql statement not working

I am trying to return voice messages from a database using this query :

SELECT m.mid, 
       m.uid, 
       m.latitude, 
       m.longitude, 
       m.user_rating, 
       m.created_at 
FROM application_Message AS m 
WHERE 
      m.longitude BETWEEN 34.657646284846685 AND 35.342353715153315 AND 
      m.latitude BETWEEN 44.757919366450686 AND 45.242080633549314;

and the database has this in it :

+--------------+---------------+---------+-------+----------+-----------+---------------------+-------------+
| mid          | uid           | message | speed | latitude | longitude | created_at          | user_rating |
+--------------+---------------+---------+-------+----------+-----------+---------------------+-------------+
| 158749857936 | 3425635465657 |   |   5.5 |       35 |        45 | 2011-10-10 00:00:00 |           7 |
| 158749857910 | 3425635465699 |   |  10.5 |       35 |        45 | 2012-11-11 00:00:00 |          10 |
| 158749857905 | 3425635465699 |   |  12.5 |       35 |        45 | 2012-11-11 00:00:00 |          10 |
+--------------+---------------+---------+-------+----------+-----------+---------------------+-------------+

Any ideas on why I am getting an empty set. I tried running it with between 0 and 100 and it works why doesn't it work if the values are closer to the ones I am looking for? I am not getting any sql errors.

Here is the table description:

+-------------+---------------+------+-----+-------------------+-------+
| Field       | Type          | Null | Key | Default           | Extra |
+-------------+---------------+------+-----+-------------------+-------+
| mid         | decimal(39,0) | NO   | PRI | NULL              |       |
| uid         | decimal(39,0) | NO   |     | NULL              |       |
| message     | blob          | NO   |     | NULL              |       |
| speed       | float         | YES  |     | NULL              |       |
| latitude    | double        | NO   |     | NULL              |       |
| longitude   | double        | NO   |     | NULL              |       |
| created_at  | timestamp     | NO   |     | CURRENT_TIMESTAMP |       |
| user_rating | int(11)       | NO   |     | NULL              |       |
+-------------+---------------+------+-----+-------------------+-------+

Upvotes: 0

Views: 45

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1500065

You're getting an empty set because you've got latitude and longitude the wrong way round in your query - you're asking for longitude between 34.xxx and 35.xxx, but all your longitude values are 45. Likewise you're asking for latitude between 44.xxx and 45.xxx, but all your latitude values are 35.

So either your data is incorrect, or you need to change your query to something like:

SELECT m.mid, m.uid, m.latitude, m.longitude, m.user_rating, m.created_at 
FROM application_Message AS m 
WHERE m.latitude BETWEEN 34.657646284846685 AND 35.342353715153315
  AND m.longitude BETWEEN 44.757919366450686 AND 45.242080633549314;

(I've deliberately switched the order of the fields being queried rather than changing the values, as then it matches the order you've shown it in the table, which makes it easier to read.)

Upvotes: 0

pilcrow
pilcrow

Reputation: 58534

Your lat/long data are 35/45 and your query is looking for lat/long ~45/~35. That is, you've got lat and long backwards in either data or SQL WHERE clause (at least, that's what it looks like on a mobile screen).

Upvotes: 1

Related Questions