Re: RE: Strange PHP + MySQL add / update issue with NULL values

That is what PHP / MySQL seems to be doing to the query once it is sent. I want to pass NULL to the column not a blank field (i.e. "" for text and 0 for numbers). If I do INSERT INTO table SET required_field ="" I get the same results. NULL means no value at all and "" is a empty string which in MySQL terms are two completely different things. I have also noticed another weird issue: UPDATE table SET required_field = NULL, notrequired_field = NULL WHERE id = '2' Results in: id required_field notrequired_field ------------------------------------------------ 2 NULL This is strange... All of my NOT NULL columns result in '' data instead of NULL but colunms that do not have a NOT NULL constraint do result in NULL! Dan > Try this instead: > > INSERT INTO table SET required_field ="" > >> -----Original Message----- >> From: Dan Tappin [mailto:dan@wavefront.ca] >> Sent: Saturday, August 17, 2002 12:56 PM >> To: mysql@lists.mysql.com; php-general@lists.php.net >> Subject: Strange PHP + MySQL add / update issue with NULL values >> >> >> I have a table where I have specified several columns which I >> have specified >> that NULL values are not allowed. >> >> I have created a PHP based create / update / delete page where users can >> update the table. >> >> I have an INSERT script that takes the user input and generates the >> following query statement. >> >> Insert: >> >> INSERT INTO table SET required_field = NULL >> >> I have set the required field to NULL which results in an error because >> naturally this is a required field. If I use an actual value the >> query runs >> fine and the row is added. >> >> >> Now I have another page that allows the user to update the existing rows. >> >> Update: >> >> UPDATE table SET required_field = NULL WHERE id = '2' >> >> This assumes that the user has cleared a required field and is >> attempting to >> enter NULL data for a required field. If I execute this directly in mysql >> the query fails (0 rows touched). >> >> If I execute this via PHP from the web page the row is updated >> but it passes >> "" instead of NULL so the row is updated. The query above is echoed right >> from the web page and pasted into MySQL. >> >> Any idea? >> >> Thanks, >> >> Dan