MySQL - How to decrement int value every 24 hours?

Question

I am working a system in PHP. In my MySQL have a table borrow. Inside borrow is borrow_status a varchar and borrow_remain which is an int value. When the user click the submit button. The query will run something like

 INSERT INTO `borrow`( `borrow_status`,`borrow_remain`) VALUES ('Borrowing',3)

How can I decrement the borrow_remain value per 24 hours then UPDATE the borrow_status to Finish when it reaches zero?

EDIT: I also have a book table, that have a book_quantity, my only concern is that the book_quantity must increment if the borrow_status is updated to Finish.

Answer 1

You can implement the above scenario following the three steps given below :

1) First you have to enable Mysql event scheduler.

SET GLOBAL event_scheduler = 1;

2) Create a procedure in mysql that will do the borrow status update related functionalities.

    delimiter //
    CREATE PROCEDURE UBS()
    BEGIN
        UPDATE borrow set borrow_remain = borrow_remain - 1 
        WHERE borrow_status='borrowing';
        UPDATE borrow SET borrow_status = 'finished'
        WHERE borrow_remain=0;

    END //
delimiter ;

3) Create an event which will be scheduled in 24 hours interval.

CREATE
EVENT UBSEvent
ON SCHEDULE EVERY 24 HOUR STARTS '2015-03-04 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
CALL UBS();

Note :

1. UBS is the procedure name.[ UBS = Update Borrow Status (just a relevant abbreviation ]
2. The event will start from tomorrow (4 March 2015) at 12:00 AM

Have a nice day!

EDIT: To increment book_count in book table whenever a borrow_status in borrow table gets updated to 'finished' you need a trigger.

Suppose you have a book table where the count of the book is denoted by book_count; Then the following trigger will do what you want.

DELIMITER $$
CREATE TRIGGER IncBookCount AFTER UPDATE ON borrow
FOR EACH ROW
BEGIN
     IF NEW.borrow_status = 'finished' THEN
            UPDATE book SET book_count = book_count+1;
     END IF;
END $$
DELIMITER;

SUGGESTION : You need to improve your database design. Otherwise you might get more troubles in designing a complex architecture in future. Stay well!

Answer 2

Have you look at Events in mySQL? You can create an event and set it to run on a particular schedule. That might be the ticket for your problem!

Answer 3

You could also set a cronjob that executes a piece of code to decrement "borrow_remain" and updates "borrow_status" accordingly. If your hosting has cPanel it's pretty easy: https://documentation.cpanel.net/display/ALD/Cron+Jobs

Answer 4

You need to have mysql version 5.5 to use event. May be your desired event looks like this:

CREATE EVENT update_borrow
ON SCHEDULE EVERY 24 HOUR
STARTS CURRENT_TIMESTAMP
DO
BEGIN
UPDATE borrow SET borrow_remain = borrow_remain - 1 WHERE borrow_remain > 0;
UPDATE borrow SET borrow_status = 'FINISH' WHERE borrow_remain = 0;
END