I am trying to create a basket, I have a table called bids, where the rows are:
productid, username, amount, date, and timestring.
and in the product table, I have:
id (productid) enddate(when the product will end) I have more but non are needed for the basket
To make the basket I am trying to individually get the highest amount (to see which user won the item) in the bids by comparing servertime to the table product (enddate) which will say, user1 has won item because he's the top amount and the product enddate is before the server date, therefore it must have ended,
I am using $_SESSION['username']; to select the username in the tablerow
For visual, I want (this won't work as I'm not sure how to do this particular
query ("SELECT * FROM bids WHERE username=? ORDER BY amount DESC EACH product AND SELECT enddate FROM products WHERE enddate < serverdatetime AND eachproduct AND paid !=0");
Whenever a user pays, I am updating the paid row in products, so then I won't have to select them again.
Or should I do two queries,
SELECT * FROM bids WHERE username=? ORDER BY amount DESC
and then some PHP to foreach and inside that foreach run another query
SELECT * FROM products WHERE id=?
and then use a $result->enddate < severdate and inside that show the products?
If I could comment I would... But you need to join the two tables before selecting data from both of them or do 2 queries
This is one where common table expressions and the windowing analytic functions are nice.
-- Common Table Expression (CTE) "MaxBids" gets the highest bid for each productid
;WITH MaxBids (productid, bid) AS (
SELECT productid
, MAX(amount)
FROM bids
GROUP BY productid
)
-- CTE "MaxBidInfo" gets all of the bid info which matches the highest
-- bid amount for each product (there could be ties)
, MaxBidInfo AS (
SELECT b.*
FROM bids AS b
JOIN MaxBids AS mb
ON mb.productid = b.productid
AND mb.bid = b.amount
)
-- CTE "AllMaxBids" ranks the bids by the date of the bid
, AllMaxBids AS (
SELECT p.*, mb.username, mb.date, mb.amount
, RANK() OVER(PARTITION BY mb.productid ORDER BY mb.date) AS n
FROM product AS p
JOIN MaxBidInfo AS mb
ON mb.productid = p.productid
WHERE p.enddate < GETDATE()
AND p.paid = 0
)
-- Final Query gets the highest ranked bid (earliest date)
-- from the "AllMaxBids" CTE
SELECT *
FROM AllMaxBids
WHERE n = 1
NOTE: While writing this, I discovered that MySQL does not support the analytic functions which were introduced in ANSI SQL 99
Hopefully this can help somebody using an RDBMS which supports these functions