Mysql Update filtering by expired date with x Days tolerance

Question

Mysql Update filtering by expired date with x Days tolerance

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I need to update only on records that are expired and adding a tolerance of x days Example
Tolerance of 5 days Today is = 11/10/2016

id = 1 expiration = 10/10/2016 // just update if today was day 15

id = 2 expiration = 06/10/2016 // perform update

id = 3 expiration = 10/10/2016 // just update if today was day 15

id = 4 expiration = 10/10/2016 // just update if today was day 15

Here is my code is not working right the rule

$query = mysql_query("UPDATE finan SET status='Vencido' WHERE status = 'Em Aberto' AND vencimento < CURDATE() + INTERVAL '$tolerencia' DAY ") or die(mysql_error());

php mysql sql

asked by anonymous 02.11.2016 / 22:32

2 answers

1

Try this, assuming the expiration column is of type "date" or "datetime"

$query = mysql_query("UPDATE finan SET status='Vencido' WHERE status = 'Em Aberto' AND vencimento + INTERVAL '$tolerencia' DAY < NOW()") or die(mysql_error());

02.11.2016 / 22:37

How to use the EventArgs of events How to show in while an array with several things

score 1 · Accepted Answer

In your example you are placing the tolerance on the current date and not on the due date. The update would look like this on the correct date:

UPDATE finan 
   SET status = 'Vencido' 
 WHERE status = 'Em Aberto' 
   AND CURDATE() > vencimento + INTERVAL'$tolerencia' day