SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complex select statement

    Hi,

    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:

    Code:
    "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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try (didn't test it)
    Code:
    SELECT 
        tblsales.saleId
      , tblsales salename
      , tblsales.saleclosingdate 
    FROM tblsales 
    INNER JOIN tbllots
    ON tblsales.saleId = tbllots.lotsaleId
    LEFT OUTER JOIN tblbids
    ON tbllots.lotId = tblbids.bidlotId
    WHERE salenabled = 'Y' 
    AND (   saleclosingdate > Now() 
         OR (    tbllots.lotantisnipe = 'Y'
             AND tblbids.bidlotId IS NOT NULL
             AND tblbids.bidsdatetime + INTERVAL tbllots.lotantisnipemin MINUTE > NOW()
            )
        )
    ORDER BY tblsales.saleclosingdate ASC

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido,

    Thank you very much for the speedy response. It almost works but I think it needs a GROUP BY statement - at the moment I'm expecting the recordset to return 3 records (sales) but I get a repeated entry for eahc LOT in each sale (i.e. Sale ID 2 is repeated 25 times which is how many LOTs are in that Sale). Would I just need to GROUP BY saleId?

    Thanks again.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ah yes, but since you're not using any group functions (like MAX(), SUM() etc) you can use SELECT DISTINCT

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for your help. It works wonderfully!

    You're a genius!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •