How do I make this MYSQL query selection?

Databases
1

Greetings,

I am trying to count up the number of winning auctions a member has won or hasn’t won.

All of the relevant data is stored in a single table called “bidtable” with columns “id, productid, member, bidamount”. This table records each and every individual bid for an item.

So far, I have built this query:

$query = "SELECT COUNT(*) as num FROM bidtable WHERE productid IN (SELECT productid FROM bidtable WHERE member = '$member' AND bidamount = (SELECT MAX(bidamount) FROM bidtable WHERE productid IN (SELECT productid FROM bidtable WHERE member = '$member')))";

My though process is to find the distinct items a member has bidded on. Then check to see if the highest bid for that item is from that member.

I think I am close but my mind is in a brain drain right now from trying to figure this out for awhile. Let me know how to do this properly.

Thanks

2

Hope this works for you:

SELECT
  COUNT(*) AS `winning`,
  (SELECT COUNT(DISTINCT productid) FROM bidtable WHERE member = '$member') - COUNT(*) AS `losing`
FROM
  bidtable
INNER JOIN
  (
  SELECT
    productid, 
    MAX(bidamount) AS `bidamount`
  FROM
    bidtable 
  GROUP BY
    productid
  ) topbids
ON
  bidtable.productid = topbids.productid
AND
  bidtable.bidamount = topbids.bidamount
WHERE
  member = '$member'
GROUP BY
  member
3

Thanks a lot for the help Dan! That works perfectly!

There is one last thing I need to do to take this code a step further. On a different page, I need to select the product ids where a member is winning (or losing) and display the product info. Winning and losing bids show up on different pages so it doesn’t have to be in a single script, two different scripts will work.

Just off the top of my head really quick, I’ve done this (for winning members) but it hasn’t worked:


SELECT * FROM products WHERE productid IN (SELECT DISTINCT productid
FROM
  bidtable
INNER JOIN
  (
  SELECT
    productid, 
    MAX(bidamount) AS `bidamount`
  FROM
    bidtable 
  GROUP BY
    productid
  ) topbids
ON
  bidtable.productid = topbids.productid
AND
  bidtable.bidamount = topbids.bidamount
WHERE
  member = '$member'
GROUP BY
  member )

Let me know if you can figure this out.

Thanks for the help
Kind regards

4

You could make that work by removing the GROUP BY clause (you don’t need the DISTINCT either).

But I’d join the product table instead.

SELECT
  products.*,
  CASE WHEN topbids.productid IS NULL THEN 'losing' ELSE 'winning' AS `status`
FROM
  bidtable
INNER JOIN
  products
ON
  bidtable.productid = products.productid
LEFT OUTER JOIN
  (
  SELECT
    productid, 
    MAX(bidamount) AS `bidamount`
  FROM
    bidtable 
  GROUP BY
    productid
  ) topbids
ON
  bidtable.productid = topbids.productid
AND
  bidtable.bidamount = topbids.bidamount
WHERE
  bidtable.member = '$member'
GROUP BY
  bidtable.productid
5

Thanks for the response Dan. Unfortunately, I could not get this code to work nor could I get the previous code to work by removing the GROUP BY statement. I tried to understand the JOIN statements as I’ve never worked with these before and have little experience with it.

SELECT * FROM products WHERE productid IN (SELECT productid
FROM
  bidtable
INNER JOIN
  (
  SELECT
    productid, 
    MAX(bidamount) AS `bidamount`
  FROM
    bidtable 
  ) topbids
ON
  bidtable.productid = topbids.productid
AND
  bidtable.bidamount = topbids.bidamount
WHERE
  member = '$member' )

I have something like this so far but it hasn’t been working as there seems to be some error.

Thanks
Kind regards

6

Put the GROUP BY of the derived table back in. It was only the outer one you want to remove, because you no longer want one row per member.

…which is?

7

Thanks for the help Dan. I was getting a common error “Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource”

I was able to figure it out though, after removing the GROUP BY statement, I had to change the first line to:

SELECT * FROM products WHERE productid IN (SELECT topbids.productid

or

SELECT * FROM products WHERE productid IN (SELECT bidtable.productid

For some reason, topbids.productid and bidtable.productid worked but plain productid did not. I am not sure why. This seems to be accurate, hopefully this is the solution.

Thanks for the help and great code.
Kind regards

8

mysql_error() would tell you the error from the MySQL server, which would be that productid is ambiguous (it doesn’t know which table’s productid you want).