Database - Date Field?

  • beaner
  • Novice
  • Novice
  • beaner
  • Posts: 15

Post 3+ Months Ago

I have a form that submits data to a MySQL database. I would like the date to automatically display in PHPmyadmin so that I can keep track of each submission. What is the best way to do this? I have a date field already added with the type set to "date". But all it displays is 0000-00-00. I have to manually run the CURDATE function or NOW() function to have it display properly. I would like to have this happen automatically if possible.

Please let me know if anybody knows how to do this.
Thanks!
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Are you generating a date somewhere that should be getting saved to the field? I assume that by run manually, you mean you're writing this as a simple query and executing it. Instead, you need to integrate it into your update or insert.
  • beaner
  • Novice
  • Novice
  • beaner
  • Posts: 15

Post 3+ Months Ago

Yes i figured it out. I put the now() function in my query script and it works great! Thanks for the response though!
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

You could also use timestamp field in mysql and it should timestamp each entry as it's added automatically which is a count of how many seconds have elapsed since a certain date. Then in your code whenever you needed the date:

Code: [ Select ]

$timestamp = $mysql_timestamp; // timestamp pulled from database

$record_date = date('Y-m-d h:m:s', $timestamp);

// or write it as a small function

function change_ts($t) {
$record_date = date('Y-m-d h:m:s', $t);
return $record_date;
}

// then use by
$converted_date = change_ts($mysql_timestamp);
  1. $timestamp = $mysql_timestamp; // timestamp pulled from database
  2. $record_date = date('Y-m-d h:m:s', $timestamp);
  3. // or write it as a small function
  4. function change_ts($t) {
  5. $record_date = date('Y-m-d h:m:s', $t);
  6. return $record_date;
  7. }
  8. // then use by
  9. $converted_date = change_ts($mysql_timestamp);



Still the way you're doing it is fine cause you just write your query and insert into date or datetime field and then you can just pull the date and have it. Though, the date format is, I think, standard in the UK so no problems there but in the US I've had many clients that needed the 'Y-m-d' changed to 'm-d-Y' for easier reading. So you still may be looking at converting the date at some future time so that's why I'd thought I'd throw this example out as you can take your auto-timestamp (so you don't have to worry about coding NOW() and putting in all your queries and I think date/datetime uses more space) and convert it to any date format you want when you want to display with that one line like date('m-d-Y', $t).
  • guitrspaz
  • Graduate
  • Graduate
  • guitrspaz
  • Posts: 106
  • Loc: Baltimore, MD

Post 3+ Months Ago

Or you could ALTER [tablename] MODIFY [datefield] TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP...I think.
  • Tannu4u
  • Proficient
  • Proficient
  • User avatar
  • Posts: 480
  • Loc: India

Post 3+ Months Ago

I agree create a field with the data type of TIMESTAMP. Once done whenever data is inserted in the table MySQL automatically inserts a UNIX timestamp for that field.

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 174 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.