I'm building an auction style site. The "auctions" are grouped into "sales" and each sale has a closing date/time. Currently to display a list of "active" (running) sales I use this simple SELECT statement:
"SELECT saleId, salename, saleclosingdate FROM tblsales WHERE salenabled = 'Y' AND saleclosingdate > Now() ORDER BY saleclosingdate ASC"
(where salenabled is a simple Y or N depending on whether the sale is enabled (over-riding the closing date/time) and saleclosingdate is the DATETIME closing date/time)
Within each "Sale" are "lots" (database table: tbllots), each lot has the following (relevant) fields:
tbllots.lotId (unique auto increment INT)
tbllots.lotsaleId (INT - which sale (saleId) this lot is related to)
tbllots.lotnum (VARCHAR 100 - a short text description of the lot)
So far, so good. However, my client would like to add an "anti-sniping" feature at LOT level. They want to enable each LOT to extend (it's closing date/time) by a set time (minutes) should anybody place a bid in the last (eg) 5 minutes to allow other bidders to have time to place another bid, only when there had been no (bidding) activity on the LOT during the last 5 minutes would the LOT close. All other LOTs would close at the closing date/time set in the sale.
So what I need is for my SELECT statement to return "Sales" which either haven't reached their closing date/time (saleclosingdate) OR sales that have passed their closing date but have any LOTs with anti-sniping enabled and if they do have anti-sniping enabled check whether any bids have been received in the last X minutes.
I've added two new fields to tbllots:
tbllots.lotantisnipe (VARCHAR 1 (Y if enabled, N if not enabled)
tbllots.lotantisnipemin (INT) value in minutes of how long to leave lot open after last bidding activity.
Bids made by clients are stored in the tblbids table, the relevant fields are:
tblbids.bidId (unique auto increment INT)
tblbids.bidsaleId (INT - which sale (saleId) this bid is related to)
tblbids.bidlotId (INT - which LOT (lotId) this bid is related to)
tblbids.biddatetime (DATETIME of bid)
I really hope someone can help me with this as I can't see anyway of makign the SELECT statement with so many conditions.
Many thanks for taking the time to read this.