php - MySQL删除行直到某个点

Question

我正在为客户构建一个拍卖网站，我遇到了一个问题，我需要从出价表中删除出价直到某个点，该点由出价者的数量或底价决定。

让我进一步解释。我有一张包含数据的表格:

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

如您所见，有 2 个竞标者在激烈竞争。现在每个出价者都可以随时取消他们的出价，但是，比如说 bidder2 取消他们的出价，系统将拍卖中的所有出价标记为已取消(客户要求)，而不仅仅是他们最近的出价。如果是这种情况(投标人 2 取消他们的出价)，那么投标人 1 的出价应该立即回滚到 1000 美元，这是拍卖的底价(你应该能够分辨出来，因为投标人 1 连续两次出价，550 美元和 1000 美元).

我希望这只是棘手的部分，而不是不可能的部分。假设我有 3 个投标人:

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

如果 bidder2 取消了他们的出价，那么我需要回滚直到 bidder3 的最新出价，但仍然 bidder1 获胜。

感谢任何指导。

Answer 1

首先统计总数投标人

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

//考虑我们要删除 bidder_id=2 出价

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");
}

希望对你有帮助。