Auto-insert of current date per record!

jonsof

I have the table below

CREATE TABLE text (
postid SMALLINT NOT NULL AUTO_InCREMENT,
userid smallint not null,
title VARCHAR(250) not null,
categoryid smallint not null,
post text not null,
image VARCHAR(20) not null,
date_posted DATE not null,
expire_date DATE not null,
primary key (postid) );

i would like the date to be inserted automatically field date_posted each time i insert values into the fileds and at the expire_date must be calculated the date (2 months after)

Is this possbile?

Thanks in advance!

cgraz

Yup! You could use now()

$query = mysql_query("INSERT INTO text (postid, userid, title, categoryid, post, image, date, expire_date) VALUES 
('$postid', '$userid', '$title', '$categoryid', '$post', '$image', now(), '$expire_date')");

to get it to add 2 months to the current date, you can try this (not sure if it will work)

$query = mysql_query("INSERT INTO text (postid, userid, title, categoryid, post, image, date, expire_date) VALUES 
('$postid', '$userid', '$title', '$categoryid', '$post', '$image', now(), DATE_ADD(now(), INTERVAL 2 MONTHS))");

On that link I gave above in the mysql manual, if you search that page for date_add you can look up more info. Not sure if you can use INTERVAL 2 MONTHS or if it has to be INTERVAL 2 MONTH. I think 2 MONTHS should work.

Let me know how that works.

Cgraz

jonsof

🙂 Eventually the now(), DATE_ADD(now(), INTERVAL 2 MONTHS) worked finally and not MONTHS!

Thank you for your help!

cgraz

So then what did your final insert query look like? (i'm just curious because I know this will come in handy soon).

Cgraz

jonsof

$query = mysql_query("INSERT INTO text (postid, userid, title, categoryid, post, image, date, expire_date) VALUES
('$postid', '$userid', '$title', '$categoryid', '$post', '$image', now(), DATE_ADD(now(), INTERVAL 2 MONTH))");

MONTH is the type, as i read in the manual of Mysql, and
MONTHS is the format namely MONTHS MONTH=>(in our case)2 MONTH.

That's all
Thanks again!