RE: [PHP-DB] MySQL, PHP or ghost?

Yeah, but Peter stated that he entered year="" and the query inserted 2000 in the column. > > mysql> update plate set year="" where pid=65; > > Query OK, 1 row affected (0.01 sec) > > Rows matched: 1 Changed: 1 Warnings: 1 > > > > mysql> select * from plate where pid=65; > > +-----+---------+------+- > > | pid | plate | year | > > +-----+---------+------+- > > | 65 | DVF0343 | 2000 | > > +-----+---------+------+- If year="" is converted to the integer 0 as you state, then, according to the MySQL manual, it should have inserted 0000 into the database, not 2000. See below: From the MySql Manual: "As a two-digit number in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999. Note that the range for two-digit numbers is slightly different than the range for two-digit strings, because you cannot specify zero directly as a number and have it be interpreted as 2000. You must specify it as a string '0' or '00' or it will be interpreted as 0000." So it looks like an empty string, year="" is actually converted to the STRING 0, resulting in the value 2000 being inserted into the table. According to the manual, the YEAR type expects/requires either a two or four digit integer or a two or four digit number as a string. If it gets anything else, it's supposed to spit out 0000. Therefore, instead of converting Peter's year="" to something else, it should just insert 0000. It sounds like a small bug in the way the YEAR type handles data. Maybe a search through the MySQL bug list would yield something in the way of an explanation. I'm just as puzzled by this as Peter is/was and I'm just interested in the explanation. I'm not trying to start an argument here. Rich > -----Original Message----- > From: Ignatius Reilly [mailto:ignatius.reilly@free.fr] > Sent: Wednesday, August 27, 2003 9:31 AM > To: Peter Beckman; php-db@lists.php.net > Subject: Re: [PHP-DB] MySQL, PHP or ghost? > > > Read the MySQL manual. > > "0" value is interpreted as 2000. > Your empty string is converted to an integer, thus 0. > > HTH > Ignatius > _________________________ > ----- Original Message ----- > From: "Peter Beckman" <beckman@purplecow.com> > To: <php-db@lists.php.net> > Sent: Wednesday, August 27, 2003 3:39 AM > Subject: [PHP-DB] MySQL, PHP or ghost? > > > > Seems that either I don't understand mysql, or something. > > > > My table, with the non-important things removed... > > > > mysql> explain plate; > > > +---------+-----------------------+------+-----+-------------- > -------+------ > ----------+ > > | Field | Type | Null | Key | > Default | > Extra | > > > +---------+-----------------------+------+-----+-------------- > -------+------ > ----------+ > > | pid | mediumint(8) unsigned | | PRI | NULL > | > auto_increment | > > | year | year(4) | YES | | NULL > | > | > > [...] > > > > So my assumption is that if I insert with year="" it should use the > > default. Or at least 0000. > > > > mysql> update plate set year=NULL where pid=65; > > Query OK, 1 row affected (0.00 sec) > > Rows matched: 1 Changed: 1 Warnings: 0 > > > > mysql> select * from plate where pid=65; > > +-----+---------+------+- > > | pid | plate | year | > > +-----+---------+------+- > > | 65 | DVF0343 | NULL | > > +-----+---------+------+- > > > > But if I do this: > > > > mysql> update plate set year="" where pid=65; > > Query OK, 1 row affected (0.01 sec) > > Rows matched: 1 Changed: 1 Warnings: 1 > > > > mysql> select * from plate where pid=65; > > +-----+---------+------+- > > | pid | plate | year | > > +-----+---------+------+- > > | 65 | DVF0343 | 2000 | > > +-----+---------+------+- > > > > 2000? What? Why? Confused. PHP or Mysql fault? > > > > Beckman > > > -------------------------------------------------------------- > ------------ > - > > Peter Beckman > Internet > Guy > > beckman@purplecow.com > http://www.purplecow.com/ > > > -------------------------------------------------------------- > ------------ > - > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >