CODEWITHSUNDEEP
phpjavascriptmysqlsql
user1152440
user1152440

Reputation: 905

Adding Timestamp to Database Table

I have an html/php form that updates entries on the database server. I need to add a field to each row indicating when that entry is added, so in other words a timestamp of when the entry was created. I have been searching and found this:

http://www.createafreewebsite.net/phpmysql/alter.html

Would I do something like:

$timestamp = time();
mysql_query("ALTER TABLE notification
ADD timestamp CHAR(30) AFTER names);

mysql_query("INSERT INTO notification (`timestamp`) values ('$timestamp');

is this the correct way to approach it, and am I using the correct datatype? I would need to compare the timestamp with another timestamp generated from a javascript file later on. For example, if timestamp1 is smaller than timestamp2 than perform following functions...

Any information would be helpful, thanks!

EDIT:

Provided information as requested:

So far I have:

mysql_query("INSERT INTO notification (`program`, `month`, `day`, `year`, `sponsor`, `type`, `category`, `range`, `desc`) values ('$pName' ,  '$month' , '$day' , '$year' , '$sponsor' , '$type' , '$category' , '$range' , '$desc')");

Upvotes: 1

Views: 5739

Answers (3)

David Faber
David Faber

Reputation: 12485

You definitely do not want to use a column with CHAR or VARCHAR datatype to store a date or timestamp - it can make comparisons difficult later on. Also, you should consider putting a default on the timestamp column so that it is automatically populated when you insert a row, or using an insert trigger on the notification table to do the population. That way the chance of developer error is reduced.

CREATE TRIGGER notification_timestamp BEFORE INSERT
    ON notification FOR EACH ROW
BEGIN
    SET new.timestamp = NOW();
END;

Apologies if the syntax isn't quite right.

Upvotes: 1

472084
472084

Reputation: 17885

time() in PHP will produce a timestamp, your MySQL table might be expecting another format, so you can just do:

mysql_query("INSERT INTO notification (`timestamp`) values (NOW());

and it will work with date and datetime fields too.

Even though your table is CHAR(30) you still have one less variable to use.

Of if you change your column data type to TIMESTAMP then you can use on update CURRENT_TIMESTAMP to fill the table cell for you.

Upvotes: 4

Stewie
Stewie

Reputation: 3121

timestamp should have a timestamp datatype. http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Upvotes: 1

Related Questions