mysql date format? help!

[Anon]

hi everyone,

new here and at mysql. please be gentle. =)

this is what i want to do..

after i insert a data into my table, i want to show a 'new' image next to it for 30 days. after 30 days, that image will disappear.

so my question is..

1. how do i insert a date value into my table (i.e. is there a way that it'll automatically insert the date?)

2. once i do that, how does my 'if' statement look like?

thanks for the help!

[Anon]

Can't help with the if statement but when you create the table select 'timestamp' as the field type for the row in question.

I suppose the if statement will check against the value (date) pulled from the data field.

[[deleted]]

No timestamp, use datetime.

when you insert, you insert a "now()" for the datetime field.

When you select the data, you can use simple date calculations to decide if the "new" should be displayed:

SELECT if(timestamp_field>datesub(now(), interval 30 day),1,0) as print_the_new_image
FROM table
WHERE bladebla...

this will return a new column called "print_the_new_image" which contains a 1 if the field is less than 30 days old, or a 0 if it is 30 or more days old.

You can then use this 1 or 0 to actually print of not print the image

[Anon]

I don't quite understand what that means above... I'm having a similar problem. I have a date field called "EndDate" and I want to run a script every now and then, and it will list all the fields that have an EndDate in the past (eg. their account has expired).

I guess I can't just to:

if ($row["EndDate"] < now()) {

}

could I? I think it's using the date thing (YYYY-MM-DD)...

Hope you can help.

Danny

[[deleted]]

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

[Anon]

🙂

Thank you very much! I did try searching this site... Maybe I should have looked over there?! DOH!

Thanks mate