MySQL delete row until certain point

Question

I am building an auction site for a client and I have a problem where I need to remove bids from the bid table until a certain point, that point is determined by the number of bidders or a 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 out. Now each bidder is able to cancel their bids at anytime however, say bidder2 cancels their bids, the system flags all bids on the auction as cancelled (client requirement) not just their latest one. If that is the case (bidder2 cancels their bids) then bidder1's bids should roll right back to $1000 which is the reserve price for the auction (as you should be able to tell because there are 2 bids in a row from bidder1, $550 and $1000).

Heres what I am hoping is just the tricky bit and not the impossible bit. 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 bidder2 cancels their bids then I need to roll back until the latest bid of bidder3 but still with bidder1 winning.

Any guidance is appreciated.

Answer 1

first count total no. of bidders

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

//consider we want to remove bidder_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 will help u.