Goarge Go Goarge Go - 4 months ago 7
PHP Question

update and select in one query in mysql using cron job

Using cron job am trying to update 30dexpf field to 1 if the expiry date is exactly 30 days far from current date. Otherwise the 30dexpf is supposed to have 0 but no success.

$query="UPDATE user_pages SET 30dexpf=1 WHERE expiry > CURRENT_DATE - INTERVAL 30 DAY";


and i also tried this but no success

$query="update user_pages 30dexpf =
(case when expiry > CURRENT_DATE - INTERVAL 30 DAY then 1 else 0 end)";


here is my db structure

| user_id | 30dexpf | expiry |

| 7 | 0 | 2016-08-30 11:27:56 |

Answer

I guessed you want to update 30dexpf field to 1 if the expiry date is exactly 30 days far from current date. Otherwise the 30dexpf is supposed to have 0.

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