I am having some issues with some integer fields and string input. The
table setup is the following:

| Field | Type | Null | Key | Default | Extra |
| intfield1 | smallint(6) | YES | | 0 | |
| intfield2 | smallint(6) | YES | | NULL | |
| intfield3 | smallint(6) | YES | | NULL | |

I have values coming into the database from an HTML form select box.
The default value from the select box is an empty string if nothing is
chosen. So we have an update statement that looks like the following:

UPDATE table SET intfield1='', intfield2='', intfield3='' WHERE ...;

This will then set everything to be 0's. Know, I know that there should
probably be better preprocessing before going to the database, but this
just brought to my attention this issue.

It seems to me that if the value that is being set is invalid for the
field it should set it to NULL or at least the default value for the
field instead of 0. Why is it set to 0 regardless?

Les

On Fri, Mar 14, 2008 at 12:04 PM, Les Fletcher <les@affinitycircles.com> wrote:
> | Field | Type | Null | Key | Default | Extra |
> | intfield1 | smallint(6) | YES | | 0 | |
> | intfield2 | smallint(6) | YES | | NULL | |
> | intfield3 | smallint(6) | YES | | NULL | |
> UPDATE table SET intfield1='', intfield2='', intfield3='' WHERE ...;
>
> It seems to me that if the value that is being set is invalid for the
> field it should set it to NULL or at least the default value for the
> field instead of 0. Why is it set to 0 regardless?
>
> Les

1. http://dev.mysql.com/doc/refman/5.0/...lid-data.html\
"If you try to store a string that doesn't start with a number into a
numeric column, MySQL Server stores 0. "
2. You are treating an integer like a string. An integer does not need
apostrophes. Without the apostrophes you would get an error. Type cast
it to an int. Better yet use a bind variable.
3. I suggest using a strict sql_mode. You would then get an error for
the blank string.



--
Rob Wultsch

Thanks for the link. That s a lot.

Rob Wultsch wrote:
> On Fri, Mar 14, 2008 at 12:04 PM, Les Fletcher <les@affinitycircles.com> wrote:
>
>> | Field | Type | Null | Key | Default | Extra |
>> | intfield1 | smallint(6) | YES | | 0 | |
>> | intfield2 | smallint(6) | YES | | NULL | |
>> | intfield3 | smallint(6) | YES | | NULL | |
>> UPDATE table SET intfield1='', intfield2='', intfield3='' WHERE ...;
>>
>> It seems to me that if the value that is being set is invalid for the
>> field it should set it to NULL or at least the default value for the
>> field instead of 0. Why is it set to 0 regardless?
>>
>> Les
>>
>
> 1. http://dev.mysql.com/doc/refman/5.0/...lid-data.html\
> "If you try to store a string that doesn't start with a number into a
> numeric column, MySQL Server stores 0. "
> 2. You are treating an integer like a string. An integer does not need
> apostrophes. Without the apostrophes you would get an error. Type cast
> it to an int. Better yet use a bind variable.
> 3. I suggest using a strict sql_mode. You would then get an error for
> the blank string.
>
>
>
>