auto increment date column

[khefner]

Ok, all I want to do is have a date column that gets updated when the record is first created by mysql.

Using phpmyadmin i selected "date" for the "type", but that didnt do it. PHPMYADMIN keeps inserting 0000-00-00 into the default setting for the date field. dont know why.

 

Thanks,

Hef

[LazyJones]

[!--quoteo(post=334406:date=Jan 7 2006, 03:59 PM:name=Hef)--][div class=\'quotetop\']QUOTE(Hef @ Jan 7 2006, 03:59 PM) 334406[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Ok, all I want to do is have a date column that gets updated when the record is first created by mysql.

Using phpmyadmin i selected "date" for the "type", but that didnt do it. PHPMYADMIN keeps inserting 0000-00-00 into the default setting for the date field. dont know why.

 

Thanks,

Hef

 

The value you are inserting must be in the same format as the DATE column. Present time in that format would be given by:

echo date("Y-m-d",time());

[fenway]

The default value of a DATE field won't do what you want. You'll need to pass "NOW()" as the value for the DATE field in question whenever you issue your INSERT statement. In principle, you could use the TIMESTAMP column type as well, but IMHO, this is a very, very bad idea, and is more trouble than it's worth.

[khefner]

[!--quoteo(post=334413:date=Jan 7 2006, 04:52 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 7 2006, 04:52 PM) 334413[/snapback][/div][div class=\'quotemain\'][!--quotec--]

The default value of a DATE field won't do what you want. You'll need to pass "NOW()" as the value for the DATE field in question whenever you issue your INSERT statement. In principle, you could use the TIMESTAMP column type as well, but IMHO, this is a very, very bad idea, and is more trouble than it's worth.

 

Fenway,

I added a field called "date" in the database with the type = "date".

 

Below is my code from my form that I am using to pass the information to the database.

When I submit, I dont get an error, but I am not updating the date field either with the current date. The date field has the default data - 0000-00-00 but not the current date.

 

I dont feel confident my insert statement is correct in regards to the $date NOW(), so I think I have a problem there. When I made the date field type = date,(with PHPMYADMIN) it automatically made the default value = 0000-00-00 Is that ok?

Thanks,

Hef

 

$sql = "INSERT INTO warriors (selectfootballcheer, participant, birthdate, school, PhoneNumber, Email, street, Town, Zip, Fathersname, Mothersname, textfield, date)

VALUES ( '$selectfootballcheer', '$participant', '$birthdate', '$school', '$PhoneNumber', '$Email', '$street', '$Town', '$Zip', '$Fathersname', '$Mothersname', '$textfield', '$date= NOW()' )";

[fenway]

There's nothing wrong with that default value, unless you want it to be NULL, in which case you have to change the column definition from NOT NULL.

 

However, your SQL statement is incorrect -- you only pass "NOW()" as the value, and nothing else; the reason you're getting the default value is because what you've passed ('$date= NOW()' ) is just a string, which can't be in a date field.

 

Try the following (UNTESTED):

 

$sql = "INSERT INTO warriors (selectfootballcheer, participant, birthdate, school, PhoneNumber, Email, street, Town, Zip, Fathersname, Mothersname, textfield, date) VALUES ( '$selectfootballcheer', '$participant', '$birthdate', '$school', '$PhoneNumber', '$Email', '$street', '$Town', '$Zip', '$Fathersname', '$Mothersname', '$textfield', NOW() )";

 

Hope that helps.

[khefner]

[!--quoteo(post=334643:date=Jan 8 2006, 07:39 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 8 2006, 07:39 PM) 334643[/snapback][/div][div class=\'quotemain\'][!--quotec--]

There's nothing wrong with that default value, unless you want it to be NULL, in which case you have to change the column definition from NOT NULL.

 

However, your SQL statement is incorrect -- you only pass "NOW()" as the value, and nothing else; the reason you're getting the default value is because what you've passed ('$date= NOW()' ) is just a string, which can't be in a date field.

 

Try the following (UNTESTED):

 

$sql = "INSERT INTO warriors (selectfootballcheer, participant, birthdate, school, PhoneNumber, Email, street, Town, Zip, Fathersname, Mothersname, textfield, date) VALUES ( '$selectfootballcheer', '$participant', '$birthdate', '$school', '$PhoneNumber', '$Email', '$street', '$Town', '$Zip', '$Fathersname', '$Mothersname', '$textfield', NOW() )";

 

Hope that helps.

 

Thanks worked perfect!