Jump to content

How to insert a null date


micksulley
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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'];

 

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.