MySQL deletes a row to a specific point

Question

I create an auction site for a client, and I have a problem when I need to remove bids from the bid table to a certain point, this point is determined by the number of bidders or the reserve price.

Let me explain further. I have a table with data:

Bidder1 $7,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $7,000.00 Sat 21 Jul 2012 12:26:34 Bidder1 $6,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $6,000.00 Sat 21 Jul 2012 12:26:11 Bidder1 $5,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $5,000.00 Sat 21 Jul 2012 12:25:34 Bidder2 $1,100.00 Sat 21 Jul 2012 12:23:53 Bidder1 $1,000.00 Sat 21 Jul 2012 12:22:33 Bidder1 $550.00 Sat 21 Jul 2012 12:22:33 Bidder2 $500.00 Sat 21 Jul 2012 12:22:23 Bidder2 $100.00 Sat 21 Jul 2012 12:22:23 

As you can see, there are 2 bidders fighting it. Now each bidder can cancel his bids at any time, however, stating that the bidder2 cancels his bids, the flag system of all bids at the auction is canceled (client’s request) not only their last. If this is the case (bidder 2 cancels his bids), then bids on bid 1 must return back to $ 1000, which is the reserve price for the auction (as you should be able to say, because there are 2 bids in a row from bidder 1, 550 and 1000 US dollars).

Here is what I hope, this is just a complex bit, not an impossible bit. Let's say I have 3 bidders:

 Bidder1 $7,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $7,000.00 Sat 21 Jul 2012 12:26:34 Bidder3 $6,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $6,000.00 Sat 21 Jul 2012 12:26:11 Bidder1 $5,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $5,000.00 Sat 21 Jul 2012 12:25:34 Bidder3 $1,100.00 Sat 21 Jul 2012 12:23:53 Bidder1 $1,000.00 Sat 21 Jul 2012 12:22:33 Bidder1 $550.00 Sat 21 Jul 2012 12:22:33 Bidder2 $500.00 Sat 21 Jul 2012 12:22:23 Bidder2 $100.00 Sat 21 Jul 2012 12:22:23 

If bidder 2 cancels his bids, I need a rollback to the last bid of bidder 3, but still with the winner. Winner.

Any guidance is appreciated.

Answer 1

The first score is total no. bidders

 $result = mysql_query("select distinct(bidder_id) from table where bid_id=1"); 

// believe that we want to remove bid_id = 2 bids

 if(mysql_num_rows($result) == 2 ) { //select min bid of bidder $row = mysql_fetch_row(mysql_query("select * from table where bidder_id=2 order by bid_price asc limit 1")); $bidder_min_amount = $row['bid_price']; $bidder_min_id = $row['id']; //find out other bidder min bid id /* Bidder1 $7,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $7,000.00 Sat 21 Jul 2012 12:26:34 Bidder1 $6,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $6,000.00 Sat 21 Jul 2012 12:26:11 Bidder1 $5,250.00 Sat 21 Jul 2012 12:25:44 Bidder2 $5,000.00 Sat 21 Jul 2012 12:25:34 Bidder2 $1,100.00 Sat 21 Jul 2012 12:23:53 Bidder1 $1,000.00 Sat 21 Jul 2012 12:22:33 Bidder1 $550.00 Sat 21 Jul 2012 12:22:33 Bidder2 $500.00 Sat 21 Jul 2012 12:22:23 Bidder2 $100.00 Sat 21 Jul 2012 12:22:23 //may be first case like this Bidder1 $75.00 Sat 21 Jul 2012 12:22:33 */ //finding out if Bidder1 $75.00 exist $row = mysql_query("select * from table where bid_price <= $bidder_min_amount and bidder_id!=2 order by bid_price asc"); if(mysql_num_rows($row) > 0 ) { mysql_query("delete from table where id > ".$row['id'] ); } else { $row = mysql_query("select * from table where bid_price >= $bidder_min_amount and bidder_id!=2 order by bid_price asc"); if(mysql_num_rows($row) > 0 ) { mysql_query("delete from table where id > ".$row['id'] ); } } } //first condition complete if total bidder is 2 else { //if n bidders just remove the bids of bidder mysql_query("delete from table where bidder_id=2"); } 

Hope this helps you.

Answer 2

It looks like you will need to handle two scenarios: the first is if the bidder for cancellation is the highest price; secondly, if the bidder is not the highest bidder.

If the cancellation bidder is the highest bidder, you can delete each bid after the first bid, including the bids of other users. If they are not the highest bids, you simply delete all your bids and leave other users alone. This is based on the examples you provided; let me know if this is wrong and i will tune in.

I assume your db structure is more or less like this:

 users id name bids id user_id auction_id price 

Before starting your deletion, I will take MAX(id) for the current auction and for canceling the tender for the current auction (suppose auction No. 1 canceling the bidder is No. 2):

 SELECT MAX(id) AS max_auction FROM bids WHERE auction_id=1 SELECT MAX(id) AS max_bidder FROM bids WHERE auction_id=1 AND user_id=2 

Then you would compare those and other processes as needed:

 if ($max_auction == $max_bidder) { // the cancelling-bidder is the highest-bidder // get the ID of their first bid so we can delete every bid after it $result = mysql_query("SELECT MIN(id) FROM bids WHERE auction_id=1 AND user_id=2"); $row = mysql_fetch_array($result); mysql_query("DELETE FROM bids WHERE auction_id=1 AND id>=" . $row[0]); } else { // the cancelling-bidder is not the highest-bidder // just remove all of their bids mysql_query("DELETE FROM bids WHERE auction_id=1 AND user_id=2"); } 

Answer 3

For more than two participants:

• The group at the price and maximum bid will offer you one line for each participant and the maximum bid.
• Delete tender bids.
• Order a table by date, then bid, group by bidders.
• Delete all lines in which the bid was entered <the lowest bid for results No. 3. This will delete all bets leading to the lowest bid, now leaving one bid for each user who is their last bet.

You have a special case when there are only two bidders:

• Delete lines of an outgoing participant.
• Search for rows where bid = reserve price
• If there are rows, delete all rows where the offer price is <Starting price. This leaves you with the last offer that matches the reserve price.
• If there are no lines, which means that the reserve has not been fulfilled, delete all lines where the rate is <max (rate). This leaves one line, which is the current maximum bid in the auction.

This is a bit verbose - you don't need to group each time, but I wrote it so that you can confirm the results when running queries from the db shell.