How to make this MySQL query work?

[peppy]

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:

$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

[peppy]

Hello, just wondering if anyone has taken a look at this, I still need help!

Thanks

[r937]

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

[peppy]

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:

$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:

$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

[peppy]

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:

$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

[r937]

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

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?

[peppy]

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

[r937]

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