CODEWITHSUNDEEP
mysqlsql
Sudha
Sudha

Reputation: 81

PHP MYSQL Return last 3 months from a date from the DB - Mysql Query

I am working on an PHP/MySQL application. I am struk with something. For example:

MySQL Table is as follows:

id   |  deadline_date | reminder1   | reminder2 | reminder3
______________________________________________________________

 1       2021-04-26

In my above table deadline_date is 2021-04-26. Then first reminder (reminder1) will be sent on 2021-02-25, the second reminder (reminder2) will be sent on 2021-03-25, and third reminder (reminder3) will be sent on 2021-04-25. So from the deadline_date these 3 reminders have to be calculated and updated on the particular fields (reminder1, reminder2 and reminder3). So the table has to be populated with the following data:

id   |  deadline_date | reminder1   | reminder2 | reminder3
______________________________________________________________

 1       2021-04-26     2021-02-25    2021-03-25   2021-04-25

Can anyone guide me on how to achieve this? i am not sure if i can achieve this from a MYSQL Query UPDATE query.

Upvotes: 1

Views: 120

Answers (3)

jeprubio
jeprubio

Reputation: 18032

You can use DATE_ADD function to set the remainders:

UPDATE mytable 
   set reminder1 = DATE_ADD(DATE_ADD(deadline_date, INTERVAL -2 MONTH), INTERVAL -1 DAY),
   reminder2 = DATE_ADD(DATE_ADD(deadline_date, INTERVAL -1 MONTH), INTERVAL -1 DAY), 
   reminder3 = DATE_ADD(deadline_date, INTERVAL -1 DAY)

Upvotes: 1

nbk
nbk

Reputation: 49410

You can use Interval for that purpose

CREATE TABLE table1 (
  `id` INTEGER,
  `deadline_date` DATE,
  `reminder1` DATE,
  `reminder2` DATE,
  `reminder3` DATE
);

✓

INSERT INTO table1
  (`id`, `deadline_date`, `reminder1`, `reminder2`, `reminder3`)
VALUES
  ('1', '2021-04-26', NULL, NULL, NULL);

✓

UPDATE table1 t1
SET t1.`reminder1` = t1.`deadline_date`  - INTERVAL 1 DAY - INTERVAL 2 MONTH,
t1.`reminder2` = t1.`deadline_date`  - INTERVAL 1 DAY - INTERVAL 1 MONTH,
t1.`reminder3` = t1.`deadline_date`  - INTERVAL 1 DAY;

✓

SELECT * FROM table1

id | deadline_date | reminder1  | reminder2  | reminder3 
-: | :------------ | :--------- | :--------- | :---------
 1 | 2021-04-26    | 2021-02-25 | 2021-03-25 | 2021-04-25

db<>fiddle here

Upvotes: 2

Gabriel Durac
Gabriel Durac

Reputation: 2770

UPDATE YourTable
SET reminder3=DATE_ADD(deadline_date, INTERVAL -1 day),
reminder2=DATEA_ADD(DATE_ADD(deadline_date, INTERVAL -1 day), INTERVAL -1 MONTH),
reminder1=DATEA_ADD(DATE_ADD(deadline_date, INTERVAL -1 day), INTERVAL -2 MONTH)

Upvotes: 1

Related Questions