1

Can anyone help me here please. Unless I'm missing the blindingly obvious, i'm total stumped.

How is this query returning that line, when I'm asking for everything less or equal to 6 in the price column, and every greater or equal to 500 in the minutes column.

  1. minutes is a varchar
  2. price is an int

price is an int

To put it briefly, what is going on here

CREATE TABLE `gg_Crates` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `price` int(11) NOT NULL,
 `minutes` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `sms` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `data` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `url` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `bb` int(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
K20GH
  • 6,032
  • 20
  • 78
  • 118

4 Answers4

2

You are getting that row because you are comparing strings. "500" >= "60" is true, because of ASCII characters order.

You have to change the type of the minutes column or parse the value when filtering data. Eg.

SELECT *, CONVERT(minutes,UNSIGNED INTEGER) AS minutes_int
...
WHERE
...
AND `minutes_int` >= 600
...

As could also try comparing the string value to the integer value directly, eg.

AND `minutes` >= 600

by removing the commas, but I suggest you to think about changing the column format, if possible, since representing minutes as a varchar(11) is not correct and will also make you occupy lots of space without reason.

marzapower
  • 5,531
  • 7
  • 38
  • 76
0

In mysql, numbers shouldn't be quoted in your query. It looks quoting 500 is causing mysql to do a string comparison between "500" and `minutes, instead of a numeric comparison.

Get rid of quotes around the numbers and it'll work as expected.

Sam Dufel
  • 17,560
  • 3
  • 48
  • 51
  • No it won't as minutes is of type varchar which should've been int (or any other numeric type). – RobIII May 17 '13 at 23:56
  • @Robill - I didn't notice that he had his minutes field as a varchar. However, my answer is still actually correct - if he had entered his numbers as numbers, mysql would have done an implicit cast of varchar => int. – Sam Dufel May 17 '13 at 23:59
0

You are comparing two strings and expecting a number comparison output. In other words, you want to do 500 >= 60 but you actually run "500" >= "60" which means STRCMP("500", "60"), which doesn't have the same result.

Cast your column as integer and make your comparison using numbers instead.

WHERE CONVERT(minutes, UNSIGNED INTEGER) >= 500

Lepidosteus
  • 11,779
  • 4
  • 39
  • 51
-1

Try using brackets ( ) in the conditions. Similar issue happened with me too. Sometimes, the engine doesn't capture the AND operator when brackets aren't used.

Safeer
  • 184
  • 3
  • 17