PHP/MySQL: Setting a field to NULL if a form field is left blank

PostFeb 11th, 2009 | Comments (3)
For those of you working with PHP and MySQL who want to set a field in MySQL to NULL if the corresponding field in an HTML form is left blank, here is a slick trick:

UPDATE table_name SET date_field=IF('$date_value'='',NULL,'$date_value')

For text fields this isn't a huge deal, but for any of the formatted field types like "date" or "decimal," setting them equal to an empty string (i.e. '') results in them having values like 0000-00-00 and 0.00. So this little "if" shorthand in MySQL is a great solution.

Comments

ferrettasJul 5, 2009
You are a lifesaver...I've spent 4 hours trying to get a work around on this on my own...16 websites later you did it. Love the fact it updates entire empty column and not just the ones I'm adding or updating now...Love it!
danzpNov 6, 2009
Oh my. This is the most elegant fix for a really thorny problem. Thanks!
LizFeb 10, 2010
This is a great fix, but have noticed that unless you specify which row you are updating, by inserting:

WHERE ID = '$id'

(for example) at the end of your SQL query, the whole column is updated to null even if there are column entries that are NOT Null.

Post a comment

Name
URL
Email
Comment