How to insert a null date

[micksulley]

I have created a form to edit fields in a database table.  This is basically what it does

sale_stamp : <input type="text" name="sale_stamp" value="<?php echo $sale_stamp;?>" > 

$sale_stamp = $_POST['sale_stamp'];

$sql = "UPDATE mytable SET sale_stamp = :sale_stamp  WHERE id = :id";
 $stmt=$db->prepare($sql);         $stmt->execute(array(
   ':id' => $id, 
	':sale_stamp' => $sale_stamp);

This works provided there are values entered but some fields may be blank and this fails for numeric fields.  The generated sql is

UPDATE asset set sale_stamp = '' WHERE id = '17' 

The table definition allows null, but when I leave the field blank in the table the generated sql has an empty string rather than null.  How do I overcome this?

[gizmola]

First off, does it matter whether or not a column is null or ''.  The answer to that very much will dictate what you might need to do here.

The difference between NULL and an empty string with mysql is an annoyance.  

Short answer:

If you need to query for values in a column that are either NULL or not NULL, then your syntax needs to be different.  The standard equality '=' check doesn't work.

For example, looking for NULL values in a name column:

SELECT * FROM test WHERE name = NULL;

Will be empty.

You have to use:

SELECT * FROM test WHERE name IS NULL;

However, if the value is an empty string:

SELECT * FROM test WHERE name = ''

Will work.

In order to update a column to NULL you need to use the exact syntax of 

UPDATE asset set sale_stamp = null WHERE id = '17' 

You mention the "generated SQL".  You don't specify what is generating the code.   With that said, I would probably first try to see if setting the value of $sale_stamp to PHP null works.

 

$sale_stamp = empty($_POST['sale_stamp']) ? null : $_POST['sale_stamp'];

 

[micksulley]

OK I have sorted it.  I created a function decOrNull() which returns either a decimal value or null, and changed

$sale_stamp = $_POST['sale_stamp'];

to

$sale_stamp = decOrNull($_POST['sale_stamp']);

and it works.  I should have seen that myself earlier

Thanks for your help anyway.

Mick

[Barand]

9 hours ago, gizmola said:

The difference between NULL and an empty string with mysql is an annoyance. 

COALESCE() comes in useful here

SELECT ... FROM tablename WHERE COALESCE(colname, '') = '';