PHP mysql auto insert timestamp

Question

Say I have a table name auto_parts with these fields. id, part, price, timestamp and I insert a new row via php as so:

$query = "insert into auto_parts(id, part, price, timestamp)
  values(1, 'axle', 200)"
mysql_query($query);

will that automatically add the timestamp. Or do I have to insert a value for timestamp myself?

Solution 1

What you need to do is declare timestamp to be of type in your sql

timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

and modify the query to

$query = "insert into auto_parts(id, part, price)
  values(1, 'axle', 200)"
mysql_query($query);

Solution 2

$query = "insert into auto_parts(id, part, price, timestamp)
  values(1, 'axle', 200, 'NOW()')"
mysql_query($query);

Solution 3

Do it in SQL itself instead of passing it ... its more efficient ... This is the corresponding post:

Auto TimeStamp new entry to DB (phpMyAdmin)

Comments

[k.honsali]

you can use only one timestamp variable per table !!

[Sven Viking]

Only one automatic timestamp per table, whether via DEFAULT or UPDATE (though I think that's changed in recent MySQL versions). You can have multiple timestamp columns, as long as no more than one is set/updated automatically. dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.htm‌​l

[Romain]

It is convenient when you have more than 1 timestamp column (and one is automatic, see Sven Viking comment for details) and want to update/insert a new data in one other timestamp column.