In this blog post, I will explore automating the tracking of WhatsApp number updates in your Koha database using triggers, ensuring that your borrower records are always up to date.
Understanding the Challenge:
Upon integrating my PHP script with Koha, adding WhatsApp numbers became straightforward for both users and circulation staff. However, a common challenge emerged—managing the timestamps of WhatsApp number updates for our borrowers (how to obtain today's list of WhatsApp-updated patrons?). I desired an automated solution that could seamlessly update the "last updated" timestamp whenever a borrower's WhatsApp number changed. The solution to my challenge lay in employing "triggers" within the database.
Setting Up the Foundation:
To commence, a few modifications were necessary within the Koha database structure. I introduced a new column named "phone_last_updated" to our existing "borrowers" table. This column would house the timestamp of the latest WhatsApp number update. Initially, I configured the default value as NULL to ensure that pre-existing records remained unaffected.
sudo mysql -uroot -pmysqlrootpassword
USE koha_library;
ALTER TABLE borrowers
ADD COLUMN phone_last_updated TIMESTAMP DEFAULT NULL;
The Magic of Triggers:
Now, let's delve into the core of our solution—the trigger. I fashioned a trigger called "tr_borrowers_before_update," set to activate before any update operation on the "borrowers" table. For each updated row, the trigger would examine whether the new WhatsApp number differed from the old one. If a disparity existed, it would automatically update the "phone_last_updated" timestamp to reflect the current date and time.
DELIMITER //
CREATE TRIGGER tr_borrowers_before_update
BEFORE UPDATE ON borrowers
FOR EACH ROW
BEGIN
IF NEW.phone <> OLD.phone THEN
SET NEW.phone_last_updated = NOW();
END IF;
END;
//
DELIMITER ;
Real-Time Outcomes:
With the trigger in place, the system adeptly tracks updates to WhatsApp numbers. Whenever a borrower's phone number undergoes a modification, the "phone_last_updated" timestamp is automatically refreshed. This real-time functionality guarantees the precision and timeliness of our database records.
Querying for Updated WhatsApp Numbers:
To harness this timestamp data, I can effortlessly query for updated WhatsApp numbers. The subsequent SQL statement retrieves rows where the "phone_last_updated" timestamp aligns with the current date, aiding us in monitoring recent changes.
SELECT phone AS 'WhatsApp Number', cardnumber, surname, userid, password, phone_last_updated
FROM borrowers
WHERE DATE(phone_last_updated) = CURDATE();
