I have a query I have written for my auction application. But the issue I'm facing is getting the highest bidder for a particular item.
Below is my query:
$query = "SELECT auctions.auctionId, quantity, startPrice, reservePrice, startTime,
endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
item_categories.categoryName as subCategoryName, superCategoryName,
item_categories.superCategoryId, item_categories.categoryId,
users.userId, users.firstName, users.lastName, bids.userId AS mbid,
conditionName, countryName, COUNT(DISTINCT (bids.bidId)) AS numBids,
COUNT(DISTINCT (auction_watches.watchId)) AS numWatches,
MAX(bids.bidPrice) AS highestBid, MAX(bids.bidPrice)as currentPrice,
case
when highestBidderId = bids.userId THEN true
else false
end as isUserWinning
FROM auctions
LEFT OUTER JOIN bids ON bids.auctionId = auctions.auctionId
LEFT OUTER JOIN auction_watches ON auction_watches.auctionId = auctions.auctionId
JOIN items ON items.itemId = auctions.itemId
JOIN users ON items.userId = users.userId
JOIN item_categories ON items.categoryId = item_categories.categoryId
JOIN super_item_categories ON item_categories.superCategoryId = super_item_categories.superCategoryId
JOIN item_conditions ON items.conditionId = item_conditions.conditionId
JOIN countries ON users.countryId = countries.countryId
WHERE auctions.endTime > now() AND auctions.auctionId IN ( SELECT bids.auctionId
FROM bids where bids.userId = bids.userId GROUP BY bids.auctionId)
GROUP BY auctions.auctionId,bids.userId
ORDER BY endTime ASC";
Now, if I use this condition to determine the highest bidder, i am getting
Outbidded, bid again to win! for all items.
if($row['endTime'] >= $now){
if($row['isUserWinning']){?>
<p class="alert-success" style="padding: 7px 7px; border-radius: 3px;">Currently the highest bidder!</p> <?php
}else{?>
<p class="alert-warning" style="padding: 7px 7px; border-radius: 3px;">Outbidded, bid again to win!</p> <?php
}
}
What am I doing wrong or how can I go about it?
