MYSQL auto update every month

Question

Im trying to create a system where the user will get new entitle pack every month from the date inserted in the system.I have 2 table 'lejer' where it contain the balance and taken pack by the user and table 'entitle' where admin control the entitlement pack user can take from the date period.My problem is,from the date period,how to auto update the balance pack from table 'lejer' every month from the date period given by admin. Eg : In september john balance pack is 1 because he is taking 2 pack,then when he want to claim for the next month, the balance will renew to 3 according to 'entitle' in the table. How to achieve that in mysql?

SQL Update entitle pack :

UPDATE lejer l
INNER JOIN entitle e ON l.season = e.season
SET l.entitle = e.entitle

Table entitle :

CREATE TABLE `entitle` (
  `id` bigint(5) NOT NULL auto_increment,
  `entitle` varchar(30) default NULL,
  `date_from` date default NULL,
  `date_to` date default NULL,
  `season` varchar(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 100352 kB; InnoDB free: 99328 kB; InnoDB free: ';
#----------------------------
# Records for table entitle
#----------------------------


insert  into entitle values 
(1, '3', '2019-09-01', '2019-12-31', '201801');

Table 'lejer' :

CREATE TABLE `lejer` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `season` varchar(10) NOT NULL,
  `entitle` int(10) default NULL,
  `taken` int(10) default NULL,
  `balance` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 100352 kB; InnoDB free: 100352 kB; InnoDB free:';
#----------------------------
# Records for table lejer
#----------------------------


insert  into lejer values 
(4, 'John', '201801', 3, 2, 1), 
(5, 'Ali', '201802', 3, 1, 2), 
(8, 'Ella', '201801', 3, 0, 3), 
(10, 'Kamal', '201802', 3, 0, 3);

MYSQL auto update every month

0 Answers