php - 从所选 User_ID 的最新记录开始更新日期范围内的所有值

Question

我需要用一个值更新特定日期及以上日期的所有记录。我使用下面的查询，但它将新值添加到所有开始日期。该值必须仅从所选日期开始添加到最新。谢谢大家。

datepicker    ammount
------------------------
2009-08-25    75 (+ 30)
2009-07-01    100 (+ 30)
2009-07-02    120 (+ 30) <-- Need to start adding new value (difference: 30) from here, up to the latest date.
2009-07-02    320
2009-07-02    100  

// MY UPDATE QUERY
------------------------
mysqli_query($con,"UPDATE mytable 
SET ammount = ammount + '$_POST[difference]' 
WHERE Supplier_id = '$_POST[Supplier_id]' 
AND datepicker BETWEEN '". $_POST['datepicker']."' 
AND CAST(now() AS DATE)
AND DateStamp = (select MAX(DateStamp) 
    FROM mytable  
    WHERE Supplier_id='" .$_POST['Supplier_id'] ."' 
    AND datepicker='" . $_POST['datepicker'] . "')
    ");

我可以使用以下查询选择上述搜索，但不能更新。我做错了什么？？

// THE SELECT QUERY:

$result = mysqli_query($con,"SELECT * 
FROM mytable  
WHERE Supplier_id='$_POST[Supplier_id]' 
AND datepicker = '" . $_POST['datepicker'] . "' 
AND DateStamp = (select MAX(DateStamp) 
    FROM mytable  
    WHERE Supplier_id='" .$_POST['Supplier_id'] ."' 
    AND datepicker='" . $_POST['datepicker'] . "') ");

Answer 1

我建议你的日期选择器可能有问题，直接将发布数据放入查询通常也不安全，所以我建议做一些数据清理，因为这也将帮助你检查之前的查询是什么您提交它，如下所示:

 $difference = (int)$_POST[difference]; //Do some better sanitization here
 $supplierId = (int)$_POST[Supplier_id]; //Do some better sanitization here
 $datepicker = $_POST['datepicker']; //Do some better sanitization here

 $query = "UPDATE mytable " .
 "SET ammount = ammount + $difference  " .
 "WHERE Supplier_id = '$supplierId' " .
 "AND datepicker BETWEEN '$datepicker' AND CAST(now() AS DATE)";

 print $query;
 mysqli_query($con, $query);

这将帮助您了解您的查询是否符合您的预期。您也可以只运行 select 语句而不是更新语句，让您的 where 子句在您的表数据中选择正确的行，此时您可以将查询转换为更新查询。