Greater Than and Less than MySql query failing with decimal field
mysql
Solution
the problem is you are wrapping column names with single quotes forcing decimal values to compare against string literals. Column names as well as tables name are identifiers not string literals so they shouldn't be wrap with single quotes.
AND `lat` > '-1.300000'
AND `lon` BETWEEN '55.555555' AND '53.000000' -- use between here
Problem
I have a database which has 3 columns: ``` user_id | lat | lon 1 |-1.403976 | 53.428692 2 |-1.353276 | 55.224692 etc etc ``` Both lat and lon are set as decimal fields. I'm running a query similar to this but it isn't filtering based on being greater than and less than the given lat/lon numbers: ``` SELECT * FROM `table` WHERE `lat` < '-1.399999' AND 'lat' > '-1.300000' AND 'lon' < '55.555555' AND > '53.000000' ``` This query just returns every row in the table and I don't know why? Is it something to do with the fields being set as decimals? I hope someone can help - i know its probably a simple answer if you know it. As per comment - here's the create table: ``` CREATE TABLE IF NOT EXISTS `members` ( `user_id` int(10) NOT NULL AUTO_INCREMENT, `lat` decimal(8,6) NOT NULL, `lon` decimal(8,6) NOT NULL, UNIQUE KEY `user_id` (`user_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=136 ; ```