SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-17-2008, 06:13 AM
| ||||
| ||||
 integers fields and strings 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      |
|
03-17-2008, 06:13 AM
| |||
| |||
| Re: integers fields and strings 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 |
|
03-17-2008, 06:13 AM
| ||||
| ||||
| Re: integers fields and strings Thanks for the link. That helps 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. > > > > |
Linear Mode
