How to create scheduler in Mysql, but the scheduler triggered by date in my computer

0
votes

I have 4 tables

CREATE DATABASE IF NOT EXISTS `b_hotel`;

USE `b_hotel`;

/*Table structure for table `booking` */

DROP TABLE IF EXISTS `booking`;

CREATE TABLE `booking` (
  `booking_id` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `tgl_book` datetime DEFAULT NULL,
  `checkin` date DEFAULT NULL,
  `checkout` date DEFAULT NULL,
  `no_kamar` int(11) DEFAULT NULL,
  PRIMARY KEY (`booking_id`),
  KEY `customer_id` (`customer_id`),
  KEY `no_kamar` (`no_kamar`),
  CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`),
  CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`no_kamar`) REFERENCES `kamar` (`no_kamar`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `booking` */

/*Table structure for table `customer` */

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL,
  `nama_customer` varchar(50) DEFAULT NULL,
  `no_telp` varchar(13) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `customer` */

insert  into `customer`(`customer_id`,`nama_customer`,`no_telp`,`email`) values 
(1,'ABC','123','[email protected]');

/*Table structure for table `kamar` */

DROP TABLE IF EXISTS `kamar`;

CREATE TABLE `kamar` (
  `no_kamar` int(11) NOT NULL,
  `no_tipe` smallint(6) DEFAULT NULL,
  `status` enum('Kosong','Terpakai') DEFAULT NULL,
  `img` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`no_kamar`),
  KEY `no_tipe` (`no_tipe`),
  CONSTRAINT `kamar_ibfk_1` FOREIGN KEY (`no_tipe`) REFERENCES `tipe_kamar` (`no_tipe`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `kamar` */

insert  into `kamar`(`no_kamar`,`no_tipe`,`status`,`img`) values 
(101,1,'Kosong','assets/img/room/standard_1.jpg'),
(102,2,'Kosong','assets/img/room/standard_2.jpg'),
(301,3,'Kosong','assets/img/room/deluxe_1.jpg'),
(302,4,'Kosong','assets/img/room/deluxe_2.jpg'),
(601,5,'Kosong','assets/img/room/executive_1.jpg'),
(602,6,'Kosong','assets/img/room/executive_2.jpg');

/*Table structure for table `tipe_kamar` */

DROP TABLE IF EXISTS `tipe_kamar`;

CREATE TABLE `tipe_kamar` (
  `no_tipe` smallint(6) NOT NULL AUTO_INCREMENT,
  `nama_tipe` varchar(30) DEFAULT NULL,
  `harga` int(7) DEFAULT NULL,
  `dewasa` smallint(1) DEFAULT NULL,
  PRIMARY KEY (`no_tipe`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `tipe_kamar` */

insert  into `tipe_kamar`(`no_tipe`,`nama_tipe`,`harga`,`dewasa`) values 
(1,'Standard',400000,2),
(2,'Standard Big',800000,4),
(3,'Deluxe',800000,2),
(4,'Deluxe Big',1600000,4),
(5,'Suite',1600000,2),
(6,'Suite Big',3200000,4);


I want change kamar.status to 'Terpakai' if date in my computer is equal with booking.checkin.

3
mysql
You have a NULL check-in date? – Strawberry

3 Answers

0
votes

You can use MySQL event scheduler.

To enable scheduler

SET GLOBAL event_scheduler = ON;

To create recurring event

  CREATE EVENT e_hourly 
ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' 
DO 
DELETE FROM site_activity.sessions;

MySQL event scheduler

0
votes

Still not work, Ill try create with every 10 second (So, i have not to wait too long).
mycode :

DELIMITER $$
CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `b_hotel`.`auto_checkin`

ON SCHEDULE

EVERY 5 SECOND
DO
    BEGIN
         IF booking.checkin = DATE(NOW()) THEN
        UPDATE kamar JOIN booking ON kamar.no_kamar = booking.no_kamar
        SET kamar.status = 'Terpakai'
        WHERE kamar.no_kamar = booking.no_kamar;
         END IF;
    END$$
DELIMITER ;

Correct me if i wrong

0
votes

Ill try to do this, but work.. correct me please. 

DELIMITER $$
CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `b_hotel`.`auto_checkin`

ON SCHEDULE

EVERY 5 SECOND
DO
    BEGIN       
        UPDATE kamar JOIN booking ON kamar.no_kamar = booking.no_kamar
        SET kamar.status = "Terpakai"
        WHERE kamar.no_kamar = booking.no_kamar AND DATE(NOW()) = booking.checkin;
    END$$
DELIMITER ;

Correct me if i wrong