[SOLVED] How to remove, or not display, the 0000-00-00 in date fields?

[sleepyw]

Total noob question.

 

My understanding is that, by default, a date field in MySQL will populate 0000-00-00 if nothing is entered.

 

My question is, can I change that in MySQL to leave that area blank if there is no value? If not, is there an easy way to code PHP to not show a 0000-00-00 without writing an if statement for every date field where this applies?

 

TIA.

[JonnoTheDev]

Yes, you should default it to NULL. Make sure the field can contain a NULL. Only defaults to 0000-00-00 if you insert an empty value or an invalid date

 

<?php
$created = "";
mysql_query("INSERT INTO table SET created=".(strlen($created) ? "'".$created."'" : "NULL")."");
?>

[sleepyw]

EDIT: to test, I erased the 000-00-00 value in a few fields and set to NULL and it appears to work.

 

However, I would need/want to run UPDATE command to make the change to the db globally. Something like:

 

UPDATE table
SET %all fields that apply% = 'NULL'
WHERE %all fields that apply% = '0000-00-00'

 

The text between the %'s would need to be a catch-all for the whole db. Is that possible? I don't understand what your code does.

[Ken2k7]

UPDATE tablename SET column1_name = 'NULL', column2_name = 'NULL';

 

For more columns, put a comma and set it to NULL.

[sleepyw]

So there's not a "catch-all" to just replace all instances of '0000-00-00' with 'NULL', regardless of the field name?

[JonnoTheDev]

yes, like you put before

UPDATE table SET whatever = NULL WHERE whatever = '0000-00-00'

[sleepyw]

No - I'm saying instead of listing out every column name where it might apply, how do I do a global"search and replace" from 0000-00-00 to NULL?

 

I can't use the field name "whatever".  Could I use SET * = 'NULL' WHERE * = '0000-00-00'?

[Ken2k7]

No you can't.

[sleepyw]

Well, bugger!

 

OK, thanks for the help guys. I guess I'll list out all the columns and try that.

[JonnoTheDev]

ah, I see

Sorry no