CODEWITHSUNDEEP
mysql
K20GH
K20GH

Reputation: 6263

MySQL Query doesn't seem to be outputting expectations

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

Upvotes: 1

Views: 74

Answers (4)

marzapower
marzapower

Reputation: 5611

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.

Upvotes: 2

Lepidosteus
Lepidosteus

Reputation: 12027

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

Upvotes: 0

Sam Dufel
Sam Dufel

Reputation: 17598

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.

Upvotes: 0

Safeer
Safeer

Reputation: 184

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.

Upvotes: -1

Related Questions