check date expiration

[aizel]

how to check date expiration?

check expire date in mysql database

date------->status
10/27/2010->approve
10/28/2010->approve
10/26/2010->approve

date now is 10/26/2010

so it will search from the table where date=datenow then update the status to finish

[mschroeder]

Is the date still stored as a string or is using a DATE column type in mysql?

UPDATE table SET column = 'finish' WHERE STR_TO_DATE( date, '%m/%d/%Y' )= CUR_DATE()

Something like that would probably work for you, assuming your structure is similar to what it was previously.

[aizel]

it is a string sir but this

UPDATE table SET column = 'finish' WHERE STR_TO_DATE( date, '%m/%d/%Y )= CUR_DATE()

will update all date that the same with current date but different status.i want to update only the status approve..

[mschroeder]

just add another condition to the query then.

UPDATE table SET column = 'finish' WHERE STR_TO_DATE( date, '%m/%d/%Y' )= CUR_DATE() AND column = 'approve'

[aizel]

UPDATE table SET column = 'finish' WHERE STR_TO_DATE( date, '%m/%d/%Y' )= CURDATE() AND column = 'approve'

thanks sir..
last question how to alert if how many fields where updated

[mschroeder]

Depends on the mysql extension you are using in php. mysql_affected_rows(), mysqli->affected_rows()

[aizel]

sir what if the problem is like this

database
date------->status
10/27/2010->approve
10/28/2010->approve
10/26/2010->approve

form
if he modify the current date to 10/27/2010

this will be the result
date------->status
10/27/2010->finish
10/28/2010->approve
10/26/2010->finish

because this code UPDATE table SET column = 'finish' WHERE STR_TO_DATE( date, '%m/%d/%Y' )= CURDATE() AND column = 'approve' only update the current date like this:
10/27/2010->finish
10/28/2010->approve
10/26/2010->approve

it not update the date exceeded

[aizel]

lol i just put < to the current date and it works :)