MySQL coding question.

Question

How would I check for a number like zero 0 in MySQL for example does user equal zero 0?

Which one if any is the correct one?

AND user = 0
AND user = '0'
AND user = '" . 0 . "'

Answer 1

It all depends on which format the column is. If it is stored as an int, then using user=0 would be correct.

If the column was stored as a char, then user='0' would be correct.

The fundamental difference is that, a char can hold letters/numbers etc. And an int will just hold numbers - and that an int 0, and a char '0' are not the same.

Answer 2

user = 0 is the way to go if you have an integer based column. The other versions work too. However, the other ones probably require a conversion from string to integer, which is an extra overhead.

Answer 3

That depends on 2 things i would say:

A) what type of column you have, for example

• char or varchar columns you can use NULL as a default value instead of 0

• if user is an integer then you could use 0

B) the sanitize of the data depending on how you are receiving/using it, for example:

• On php if that is a field reiceved from a user you should use myql_real_escape_string to make sure there is no malicious data on it

Your last example would not work as it would be looking for ".0." you prob meant something like '"'.0.'"' escaping the quotes or whatever but that really depends on the programming you are using, if you are doing it by hand direct on the database i don't belive you would be foul enough to type something like "0;DROP TABLE BLA BLA" so no worries there.