I am using the MySQL date type for birthdays. I also use MySQL Front for my DB GUI. I have the "Not Null" check box unchecked and use no default value. Yet, no matter what I do, the DB insists upon sticking a "0000-00-00" in this field for any record that I do not explicitly write to!
Most date functions (including mine) do NOT like a date as "0000-00-00". Why won't the DB just leave this field blank per my settings? Is this thing brain dead, or what?
Brain dead? Hmmm...it is MySQL 
Are you sure you don't have a DEFAULT set on the column that is '0000-00-00'.
I agree, it's really bad practice to use MySQL's 0 date at all - it makes it a nightmare to port to other databases and it's totally non-standard...
Chris
<?
$now = date('YmdHis');
//or YmDhis, i didnt memorize these things
mysql_query("insert into table ('birthday') values ('$now')");
note:
table.birthday is a TIMESTAMP,
i always use timestamp, it is a 10 character string year(4),month(2),day(2),hour(2),minute(2),second(2)
when i call this field i use substr() to show the exact year,month and day from this 10 character string
If you are using DATETIME or DATE column types in MySQL, you have to tell it what to put into the column, it's not like TIMESTAMP where it will automatically increment, like this:
INSERT INTO tablename
(var1,var2,var3,date)
VALUES
('Some', 'Some1', 'Some2', NOW());
That will fill in the DATE as 20020819, but when you ask MySQL for the field value you will get 2002-08-19 (and 09:28, if you are using DATETIME).
EDIT: Should have said "NOW()" as a MySQL function that works with these column types, so you don't have to structure the date in PHP first.
Cheers,
V0o
With the new look to this forum (reminds me of Delphi) I can't find a way to reply directly to another member, but this is for the person who was asking about defaults.
No, I made sure the default field was blank. Just why the hell MySQL insists on sticking a "0000-00-00" in there is beyond me. I've never seen any database that does this, esp. after you've set the column to accept nulls.
Per the others, I don't want to use a timestamp for birthdays, because everytime that record is updated, the birthday will have the current date! I found this out the hard way with other columns being set for a timestamp. It was driving me crazy trying to find where my program was writing to this column until I read up on this data type.
Have you tried inserting a NULL?
I assume you mean typing in a '' or a "" into the default value?
I'll try it and see what happens.
No, I mean a NULL. like this:
insert into mytable (textfield, datefield) values ('thisis text', NULL);
If you aren't familiar with what NULLs are you might wanna lookup a good database book like something by Joel Celko (the great SQL god). :-)
Hope I didn't come off snippy in my last post, I certainly didn't mean too, but looking at it, well...
Anyway, some more stuff on NULLs.
A NULL in a database stands for "undefined" it's different from blank, in that it COULD mean anything, but no one knows. It's kinda like the marks monks made in the margins of the texts they translated when they weren't sure what a word meant.
For instand, someone asked "What would happen if a baby died before he was baptized, would he go to heaven or hell?" The monks didn't know, so they wrote it into the margin. The latin for margin is limbo.
Many decades later, folks who didn't know what the word limbo stood for assumed there was a place called limbo, and that's where purgatory, perdition, and all the other "in between" places of xtianity came from.
To point out how this applies to databases, people often mistake NULL to be a value. It is in fact an unknown value, and therefore gets treated differently from blanks, or anything else.
For instance, the proper test for a null is:
select * from table where field IS NULL
not
select * from table where field=NULL
Since NULL is not a value, nothing can equal it, so the second test is not correct. This doesn't stop some databases (COUGH ORACLE COUGH) from pretending they are, but to make portable code, NULLs need to be treated like an adjective, not a value.
So the only value you can assume that NULL is the same as is limbo. 
Just had to comment on the post by Sxooter about the monks and limbo... that's interesting... and i enjoyed reading about it...
Now, to be on topic.. hehe
instead of using the date, datetime data type in mysql, i have often found it easier to just insert my timestamp as so:
$sql="INSERT INTO tbl_name (birthday) VALUES (".time().")";
$result = mysql_query($sql);
Where the column "birthday" is of data type INT(10).
That way, when i pull the information, i can then format it using the date() function in php. Keep in mind that the second arg that date accepts is a timestamp. Here is an example:
$sql="SELECT birthday FROM tbl_name WHERE id=1";
$result = mysql_query($sql);
// Will echo out the date in this format:
// Mon Aug 31, 2002 (4:26 pm)
date('D M j, Y (g:i a)',$result['birthday'])
Now, i have had no problems porting this to any other db, since all the db's that i've worked with accept an INT (10) unsigned.
btw Stinger51, to reply to someone in particular, just click on the "quote" image that should run along the bottom of the post you wish to direct your comments at...
I created a date field in my database as a DATE type. I would like to have the current date entered into that field everytime a record is created. I don't need it to be updated when the record is updated. I used the example provided here but I still can't get it to work. Here's what my SQL Query looks like:
$sql = "INSERT INTO tablename (date, field1, field2) VALUES ("NOW()", "$field1", "$field2");
what's wrong with this? is there any better way of doing it?
I don't know. Maybe the function name needs to be in lower case (i.e. now() instead of NOW())
I use NOW() without " " or CURRENT_DATE, what's beyond me is that when I made some programs years ago using an old version of mysql I put timestamps in everything and that worked, I updated the server version and now that same programs everytime I update a value every timestamp in the table seems to update itself to the current timestamp!!!
I've had this problem before as well. If you're using PHPMyAdmin then there's no way around it. If you're doing the query, just exclude the field name from the query and don't specify a value.