0

I have tried to Google this so I didn't have to ask, as i'm sure this is a simple task...

I am building an E-commerce site and would like to add the date and time a product is added into the product database?

Apologies if this is simple, but i have researched everywhere else first.

Thanks

Shane
  • 244
  • 1
  • 4
  • 17
  • What exactly is the issue you're having? Have you tried using the built-in functions? `date('d-m-Y');` or [DateTime class](http://php.net/datetime)? – Amal Murali Sep 10 '13 at 13:16
  • Shane, that's a good question, but there are a lot of different solutions. If you can give us more information we can try to suggest ones which fit your situation. For example, are you writing raw PHP, or are you using a framework? Which library are you using to talk to MySQL? – pjmorse Sep 10 '13 at 13:16
  • Please check http://php.net/manual/en/ref.datetime.php – Ajith S Sep 10 '13 at 13:17
  • @pjmorse - I'm not sure as i am new to all this. I am writing raw PHP – Shane Sep 10 '13 at 13:19
  • Then you can use php date functions – Ajith S Sep 10 '13 at 13:20

2 Answers2

4

This can just be part of your database architecture:

ALTER TABLE  `products` ADD  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

This will automatically add a timestamp to the column created whenever a row is created. For more information, try this: Automatic Initialization and Updating for TIMESTAMP

Obviously, in this case the table is called products and you would need to change it to whatever your table name is.

UPDATE To update all existing records at the same time, just run:

UPDATE `products` SET `created` = NOW()

If you want to be more specific use:

UPDATE `products` SET `created` = NOW() WHERE `created` = '0000-00-00 00:00:00'
Brendan Bullen
  • 11,607
  • 1
  • 31
  • 40
  • 1
    This is the best approach. Let the database do the work, don't worry about doing it in PHP. – pjmorse Sep 10 '13 at 13:23
  • Brilliant! Thanks, All previous products now show as `0000-00-00 00:00:00` Is there a query to change all these to `2013-09-10 14:25:27`? – Shane Sep 10 '13 at 13:28
  • @Shane See my update. Just a simple matter of setting all records to the current date/time. If you specifically want that timestamp, just change `NOW()` to `'2013-09-10 14:25:27'` – Brendan Bullen Sep 10 '13 at 13:30
  • @BrendanBullen - Fantastic, Thanks so much. Finally (sorry) Can you help with this, I have a list which should show the 10 newest products, how would I now do this (now i have date and time recorded) Here is my current code `$result=mysql_query("SELECT * FROM products LIMIT 1,10")` – Shane Sep 10 '13 at 13:33
  • @Shane You need to order the results in your query. Try: `$result=mysql_query("SELECT * FROM products ORDER BY created DESC LIMIT 0,10")` – Brendan Bullen Sep 10 '13 at 13:35
  • @Shane Also, since you're just starting out, it would be best not to go down a bad road as far as development goes. now is the chance to avoid using `mysql_*` functions like `mysql_query`. They are deprecated and there are better alternatives. Check out this question on the subject: http://stackoverflow.com/questions/13944956/the-mysql-extension-is-deprecated-and-will-be-removed-in-the-future-use-mysqli – Brendan Bullen Sep 10 '13 at 13:39
  • @BrendanBullen - I don't suppose you have the queryto hand for adding a new column to the database do you – Shane Sep 11 '13 at 15:13
  • @Shane My answer above is the query for adding a column (in this case it adds a column called `created`). It very much depends on the type of column you require. You should read this for more information on altering a table: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html – Brendan Bullen Sep 11 '13 at 15:28
  • Thanks @BrendanBullen I am grateful for all your help – Shane Sep 11 '13 at 15:32
0

Method1 : Pass the Current DateTime as parameter to the Insert

Method2: Set the default value for the date time column in Product table

Bala
  • 618
  • 5
  • 21