Goarge Go Goarge Go - 17 days ago 6
MySQL Question

keep database flag 1 until item expiry date between in CURDATE() + INTERVAL 7 DAY AND CURDATE() + INTERVAL 30 DAY

I'm trying to keep database flag

30dexpf = 1
until expiry date of item is equal to or in between up coming 7 days and 30 days
Here is my database table

30dexpf | expiry
--------|--------
0 | 2016-12-15 14:18:00
0 | 2016-12-17 14:18:00


As far I know it can be something like this:

$query="UPDATE user_pages
SET `30dexpf` = CASE
WHEN (
DATE(expiry) = CURDATE() + INTERVAL 7 DAY
) THEN 1 ELSE 0 END;";

Answer

Try with the following query:

UPDATE user_pages
SET 
    `30dexpf` = 1
WHERE
    DATEDIFF(expiry, CURDATE()) >= 7 AND DATEDIFF(expiry, CURDATE()) <= 30;

It will update all the records whose expiry date in between 7 to 30 days from the current date.

Comments