php - MySQL default date() + 14 days, for a column?

Question

I was wondering if the following is possible to do through MySQL or will it have to be done using PHP.

Task - "Expiry Date"

1. User enters product name
2. User clicks submit form button
3. Data is POST'ed and then sent to MySQL
4. Expiry date = date now + 14 days

What I am trying to achieve is a way for mysql to insert an "expiry_date" in a table column that will equal 14 days after the date the row was created in that table.

e.g.

product_name - foo
entry_date - 2012-02-01
expiry_date - 2012-02-15

I have a feeling it may not be possible to do in mysql unless using a stored procedure.

I am happy to do it in PHP however I was hoping if I could do it with mysql it would leave less scope for error in my system.

Thanks

Answer 1

Create a table and set up a trigger for that table.

CREATE TABLE product(
    product_id INT PRIMARY KEY,
    product VARCHAR(40),
    entryDate DATETIME,
    expDate DATETIME
);

CREATE TRIGGER test_trigger BEFORE INSERT ON `product` 
FOR EACH ROW SET
    NEW.entryDate = IFNULL(NEW.entryDate, NOW()),
    NEW.expDate = TIMESTAMPADD(DAY, 14, NEW.entryDate);

On each insert into the table, the trigger sets the entryDate to the current time and expDate to 14 days time.