Jump to content

Archived

This topic is now archived and is closed to further replies.

khefner

auto increment date column

Recommended Posts

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

Share this post


Link to post
Share on other sites

[!--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());

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

[!--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()' )";

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

[!--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!

 

Share this post


Link to post
Share on other sites

×

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.