Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...