NULL variable

nikko50

I have a date field with a NULL default. How can I assign a variable as NULL so I can update that field and make it NULL again in mysql??

devinemke

UPDATE table SET field = NULL

nikko50

Hi there. I was looking more so in an update query like below. How can I assign the requiredate as a NULL

$query= "UPDATE order SET projectnum= '$projectnum',
requiredate='$requiredate',
orderedby='$orderedby',
WHERE orderid='$orderid'";

devinemke

$query = "UPDATE order SET
projectnum = '$projectnum',
requiredate = NULL,
orderedby = '$orderedby',
WHERE orderid = '$orderid'";

or perhaps...

if (isset($_POST['requiredate'])) {$requiredate = "'" . $_POST['requiredate'] . "'";}
else {$requiredate = 'NULL';}

$query = "UPDATE order SET
projectnum = '$projectnum',
requiredate = $requiredate,
orderedby = '$orderedby',
WHERE orderid = '$orderid'";

nikko50

Can't figure out when I go update my date field it shows 0000-00-00. I'm using the below query and my date field shows NULL, DEFAULT NULL.

$query = "UPDATE order SET
projectnum = '$projectnum',
requiredate = NULL,
orderedby = '$orderedby',
WHERE orderid = '$orderid'

bodzan

...by default.

You cannot give date field a NULL value. Maybe if you need it to be a NULL change the filed type to, say, VARCHAR or something that allows NULL as a value...

nikko50

Hi guys. Thanks for all the help. So if I change the field back to a default of 000-00-00, how do I test to see if it contains a value?
Can I just do this...

if($line(date)=='0000-00-00'){
echo "no value"}
if($line(date)!='0000-00-00'){
echo "yes value"}

How would you guys do it??
Tracy

devinemke

Originally posted by bodzan
...You cannot give date field a NULL value.

this is absolutely not true (not with mySQL anyway). mySQL date and datetime fields can absolutely take default NULL values.

bodzan

"The date and time types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these has a range of legal values, as well as a ``zero'' value that is used when you specify a really illegal value"

"When MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type (see the start of this section), it converts the value to the zero'' value for that type. (The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range.) The following table shows the format of thezero'' value for each type"

Column type "Zero'' value

DATETIME '0000-00-00 00:00:00'

DATE '0000-00-00'

TIMESTAMP 00000000000000 (length depends on display size)

TIME '00:00:00'

YEAR 0000

devinemke

the original question dealt with defining NULL as the default value for a date field, which is perfectly legal with mySQL. that excerpt from the manaul says "Each of these has a range of legal values", which includes NULL. as it says, if you enter a "illegal" value mySQL will fill with 0's but that has nothing to with the default value.

bodzan

I figure that he wanted to assign NULL again after it was something else...

If not then my istake...