How can I make a condition inside a query using MySQL

Question

I am building an auction system. And I am in the process that will display every bid transaction that a user has. In my table view I want to show if a certain bid transaction is the highest bid and I want to identify also if there is a higher bid than the user's bid.

Here's my query:

SELECT 
pb.id AS bid_id,
pb.product_id AS product_id,
pb.bidding_date AS bidding_date,
MAX(bidding_price) AS bidding_price
FROM auction_product_bidding AS pb
LEFT JOIN auction_product AS p
ON(pb.product_id = p.id)
WHERE pb.user_id = 1
AND p.datetime_end > NOW()
AND p.`status` = 0
GROUP BY pb.product_id;

In this query I can get the last bid of a certain user.

+--------+------------+---------------------+---------------+
| bid_id | product_id | bidding_date        | bidding_price |
+--------+------------+---------------------+---------------+
|     55 |          4 | 2016-08-01 11:50:51 |     118000.00 |
|     74 |         13 | 2016-08-11 14:14:25 |        202.00 |
+--------+------------+---------------------+---------------+

I want to add another column beside bidding price that will identify if there's a much more higher bid.

If the user has the highest bid I want to add a status like 'First Place' and if there is a much more higher bid it will display 'Bid Again'

Is it possible in a query? I read about control flow but I don't know if I can use this. If not possible maybe I will do this on the PHP side.

Thats all I hope you can help me.

Your query is incorrect or makes no sense. Either a user can only bid once per auction (product_id), but then you don't need MAX on bidding_price, or a user can make several bids, but then you are just showing one of the user's bids (bid_id, bidding_date) arbitrarily chosen. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 16, 2016 at 6:41
BTW: Why are you using a left join here? That makes no sense. And comparing end time and status in your where clause renders this a mere inner join anyway (because these condition dismiss any outer-joined rows). — Thorsten Kettner
– Thorsten Kettner, Commented Aug 16, 2016 at 7:13

Thorsten Kettner · Accepted Answer · 2016-08-16 06:26:23Z

2

User's bids:

select *
from auction_product_bidding
where user_id = 1

Current auctions:

select *
from auction_product
where datetime_end > now()
and status = 0

Highest bids:

select *
from auction_product_bidding
where (product_id, bidding_price) in
(
  select product_id, max(bidding_price) as max_price
  from auction_product_bidding
  group by product_id
)

The three combined:

select 
  p.product_name,
  usrpb.id as bid_id,
  usrpb.product_id as product_id,
  usrpb.bidding_date as user_bidding_date,
  usrpb.bidding_price as user_bidding_price,
  max(usrpb.bidding_price) as user_bidding_price,
  maxpb.bidding_price as max_bidding_price,
  maxpb.bidding_price as max_bidding_price,
  case when usrpb.user_id = maxpb.user_id then 'yes' else 'no' end as user_is_max_bidder
from auction_product_bidding usrpb
join auction_product p on p.id = usrpb.product_id
join
(
  select *
  from auction_product_bidding
  where (product_id, bidding_price) in
  (
    select product_id, max(bidding_price) as bidding_price
    from auction_product_bidding
    group by product_id
  )
) maxpb on maxpb.product_id = usrpb.product_id
where usrpb.user_id = 1
and p.datetime_end > now()
and p.status = 0;

answered Aug 16, 2016 at 6:26

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerielle Over a year ago

Thanks for the help. But there is an error. When I have 2 bid items that I am the highest it only shows 1 record. :(

Collectives™ on Stack Overflow

How can I make a condition inside a query using MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related