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 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.
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?
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
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?