SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to make this MySQL query work?

    Greetings,

    I am trying to set up an auction admin panel for each member and display a list of items where the bidder lost: from being out-bidded or not meeting the reserve price.

    I have an "items" table which is a list of every item for sale and a "bids" table that records every bid by every member.

    I've come up with this so far:

    Code:
    $member = $_SESSION['member'];
    
    $sql = "SELECT * FROM items WHERE id IN (SELECT bids.itemid FROM bids INNER JOIN (SELECT itemid, MAX(bid) AS `bid` FROM bids WHERE bidder = '$member' GROUP BY itemid) topbids ON bids.itemid = topbids.itemid AND bids.bid = topbids.bid WHERE (bidder != '$member' OR (bidder = '$member' AND items.reserveprice > items.currentprice))) ORDER BY endtime";
    This code seems to work in displaying all of the items where a member was the highest bidder but they did not meet the reserve price. It is not displaying items where a member was out-bidded though.

    Pleases take a look at this and let me know what the problem might be.

    Thanks

  2. #2
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello, just wondering if anyone has taken a look at this, I still need help!

    Thanks

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i have a suggestion...

    write two queries --

    1. member was highest bidder but did not meet reserve price
    2. member was out-bidded

    test them separately to make sure they're working

    then post them and we'll try to combine them into a single query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response. That is one of the problems, I am able to get members that were the highest bidders but did not meet the reserve price, but I am having trouble getting members that were out-bidded as it keeps giving 0 results:

    Member was highest bidder but did not meet reserve price:
    Code:
    $sql = "SELECT * FROM items WHERE id IN (SELECT bids.itemid FROM bids INNER JOIN (SELECT itemid, MAX(bid) AS `bid` FROM bids WHERE bidder = '$member' GROUP BY itemid) topbids ON bids.itemid = topbids.itemid AND bids.bid = topbids.bid WHERE bidder = '$member' AND items.reserveprice > items.currentprice) ORDER BY endtime";
    Member was out-bidded:
    Code:
    $sql = "SELECT * FROM items WHERE id IN (SELECT bids.itemid FROM bids INNER JOIN (SELECT itemid, MAX(bid) AS `bid` FROM bids WHERE bidder = '$member' GROUP BY itemid) topbids ON bids.itemid = topbids.itemid AND bids.bid = topbids.bid WHERE bidder != '$member') ORDER BY endtime";
    The code that finds the members that were outbidded should be working as far as I know. Let me know if you can spot something wrong there.

    Thanks

  5. #5
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Greetings!

    I think I figured it out, it became easier once I started building a table for each step one at a time, visualizing the path and finding a solution:

    Code:
    $sql = "SELECT * FROM items WHERE id IN (SELECT bids.itemid FROM bids JOIN (SELECT itemid, MAX(bid) AS maxbid FROM bids WHERE itemid IN (SELECT itemid FROM bids WHERE bidder = '$member') GROUP BY itemid) topbids ON bids.itemid = topbids.itemid AND bids.bid = maxbid WHERE bidder != '$member' OR (bidder = '$member' AND items.reserveprice > items.currentprice)) ORDER BY endtime DESC";
    This is probably the largest and most complex queries I've built up from scratch... Thankfully, it's the largest one on my site, one I've never truly understood and had nightmares about if it wasn't working like I had assumed...

    Now that the full solution is here, can you see any further way to optimize it, make it use less resources?

    Thanks
    Kind regards

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    speaking of visualizing...

    may i suggest that you learn to use indentation and line breaks when writing sql

    writing everything on a single line is ~hell~ to understand and debug

    you will definitely thank me later for this advice
    Code:
    SELECT * 
      FROM items 
     WHERE id IN 
           ( SELECT bids.itemid 
               FROM bids 
               JOIN ( SELECT itemid
                           , MAX(bid) AS maxbid 
                        FROM bids 
                       WHERE itemid IN 
                             ( SELECT itemid 
                                 FROM bids 
                                WHERE bidder = '$member') 
                      GROUP 
                          BY itemid ) topbids 
                 ON bids.itemid = topbids.itemid 
                AND bids.bid = maxbid 
              WHERE bidder != '$member' 
                 OR (
                    bidder = '$member' 
                AND items.reserveprice > items.currentprice
                    )
           ) 
    ORDER 
        BY endtime DESC
    as for optimization, you need to do that only if your performance is suspect

    do you know how to run an EXPLAIN?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jul 2007
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Luckily, on my web editor (Dreamweaver), the lines wrapped into 4 lines instead of one giant one. Plus I had bits of the code spread out like a puzzle and then I just combined it all at the end.

    I've actually never heard of EXPLAIN before and I do not know how to run it. How does EXPLAIN work?

    Thanks

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    put the word EXPLAIN in front of the word SELECT and run it

    there are tutorials which will teach you how to interpret what it shows

    e.g. http://assets.en.oreilly.com/1/event...esentation.pdf
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •