Jump to content


Photo

auto increment date column


  • Please log in to reply
5 replies to this topic

#1 khefner

khefner
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 07 January 2006 - 08:59 PM

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

#2 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 07 January 2006 - 09:13 PM

[!--quoteo(post=334406:date=Jan 7 2006, 03:59 PM:name=Hef)--][div class=\'quotetop\']QUOTE(Hef @ Jan 7 2006, 03:59 PM) View Post[/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
[/quote]

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


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 January 2006 - 09:52 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 khefner

khefner
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 09 January 2006 - 12:03 AM

[!--quoteo(post=334413:date=Jan 7 2006, 04:52 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 7 2006, 04:52 PM) View Post[/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.
[/quote]

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

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 January 2006 - 12:39 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 khefner

khefner
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 19 January 2006 - 04:00 AM

[!--quoteo(post=334643:date=Jan 8 2006, 07:39 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 8 2006, 07:39 PM) View Post[/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.
[/quote]

Thanks worked perfect!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users