I'm building an auction site (much like eBay) where you can create auctions with a given base value and a duration.
This is done with PHP and MySQL essentially. In this defined period of time, users can place bids.
What means should I use to be able to known asynchronously when the auction ends in order to notify the auctioneer (calling a PHP script or trigger another kind of event)?
EDIT:
I have a cronjob up and running (every 5 minutes). My PHP script calls a MySQL stored procedure that selects all invalid auctions (AuctionEndDateTime < NOW()) and updates them as closed.
Since I'm doing a SELECT in a stored procedure, I was expecting to have the updated rows returned but it's only returning the first SELECTthat I do to fech the current timestamp.
DELIMITER //
CREATE PROCEDURE closeAuctions ()
BEGIN
SELECT @TIMESTAMP_NOW := NOW();
START TRANSACTION;
SELECT * FROM Auctions WHERE auction_deadline < @TIMESTAMP_NOW AND auction_closed = '0' FOR UPDATE;
UPDATE Auctions SET auction_closed = '1' WHERE auction_deadline < @TIMESTAMP_NOW;
COMMIT;
END //
DELIMITER ;
Returns:
+-------------------------+
| @TIMESTAMP_NOW := NOW() |
+-------------------------+
| 2013-07-26 15:57:20 |
+-------------------------+
I want the updated rows because they contain information that I need to notify the auctioneers and I was trying to do this with a stored procedure so I would just make one trip to the database.
SOLVED:
DELIMITER //
CREATE PROCEDURE closeAuctions ()
BEGIN
DECLARE now TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
START TRANSACTION;
SELECT * FROM Auctions WHERE auction_deadline < now AND auction_closed = '0' FOR UPDATE;
UPDATE Auctions SET auction_closed = '1' WHERE auction_deadline < now;
COMMIT;
END //
DELIMITER ;
Write a script and inside the script query auctions from database where end time is equal to now. Then loop through the list of found auctions and send emails to owners.
To automate this process, run this script via a CRON every minute or so.