1

At the time a customer places an order on my website, how can I make it so the "order_date" column is automatically populated with the date the data is added to the database?

Is this something I have to include in my update statement or can I somehow make the database automatically add a date to when the row was inserted?

Thank you!

Manohar
  • 31
  • 10

2 Answers2

1

Use this MySQL query to make default value as CURRENT_TIMESTAMP for the required field:

ALTER TABLE `table` CHANGE `orderDate` `orderDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;

This is if you're running MySQL <5.6.5. Above, you can have your field to be of DATETIME and therefore run the following query:

ALTER TABLE `table` CHANGE `orderDate` `orderDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
D4V1D
  • 5,805
  • 3
  • 30
  • 65
  • I am running "ALTER TABLE `orders` CHANGE `OrderDate` `OrderDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" and get this error #1067 - Invalid default value for 'OrderDate'. –  Sep 11 '15 at 07:58
  • What is the type of your `orderDate` field? [It must by of `TIMESTAMP` if you're running MySQL <5.6.5, it can be `DATETIME` above this version](http://stackoverflow.com/a/168832/2788131). – D4V1D Sep 11 '15 at 07:58
  • The OrderDate field type is DATETIME. The length/value is 50. –  Sep 11 '15 at 08:01
  • What version of MySQL are you running? – D4V1D Sep 11 '15 at 08:01
  • ##MySQL client version: 5.1.73. –  Sep 11 '15 at 08:02
  • Therefore, your field must be of `TIMESTAMP` type. I have edited my answer according to your needs. – D4V1D Sep 11 '15 at 08:02
  • Spot on. Thank you very much David! –  Sep 11 '15 at 08:03
0

You can make the database automatically add a date to your datetime column when the row was inserted using now() function of php.

 <?php  
$query="insert into tableName (other_columns, date_column) values ('$valueForOtherColumn', now())";
?>
Ketan Joshi
  • 101
  • 10