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 SELECT
that 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 ;