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:

.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 pare 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')");

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/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 pare 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')");

• php
• javascript
• mysql
• sql

• Would you mind posting the structure of the table to which you're trying to add the column? Using that INSERT statement won't insert any data into the other columns of the table, and will fail if one or more of them is NOT NULL and without a default. – David Faber Commented Mar 20, 2012 at 14:01
• I've added my current insert line – user1152440 Commented Mar 20, 2012 at 14:43

3 Answers 3

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.

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

You definitely do not want to use a column with CHAR or VARCHAR datatype to store a date or timestamp - it can make parisons 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.