我有这个PHP:
$adj_index = $currentSignup + 1 - $r;//$r=3 and $currentSignup=24
for($i=1; $i<$referrals; $i++){
$current_index = $currentSignup + 1 - $i;
$q = "SELECT signup_id FROM app_sign_ups WHERE (adjusted_index='$current_index' AND app_id='$app_id')";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q'n<br />MySQL Error: " . mysqli_error($dbc));
$next_index = $current_index + 1;
if (mysqli_num_rows($r) == 1){
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
$signuper = $row['signup_id'];
$q = "UPDATE app_sign_ups SET adjusted_index='$next_index' WHERE (app_id='$app_id' AND signup_id='$signuper')";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q'n<br />MySQL Error: " . mysqli_error($dbc));
}
}
$q = "UPDATE app_sign_ups SET adjusted_index='$adj_index' WHERE app_s_id='$app_s_id'";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q'n<br />MySQL Error: " . mysqli_error($dbc));
它应该在我的数据库中更新行。每一行都有一个从1到25的值(adjusted_index
)。有了它,我将数字25变为22(通过从原始数字[25]中删除$r
),并将之前的22-24分别移动一个(因此到23,24,25)。由于某些原因,当我运行它的时候结果是22 24 25 22而不是我想要的23 24 25 22。我已经在其他几个组合上完成了它,似乎有adjusted_index
的行被最后一行取代(22被25取代)。
只运行一条UPDATE
语句,一次:
$q = " UPDATE app_sign_ups
SET adjusted_index = adjusted_index
+ CASE WHEN adjusted_index < 25
THEN 1
ELSE - '$r'
END
WHERE adjusted_index >= 25 - '$r'
AND app_id = '$app_id'
ORDER BY adjusted_index
";
或者,更好的是,使用2个UPDATE
语句:
$q1 = " UPDATE app_sign_ups
SET adjusted_index = adjusted_index + 1
WHERE adjusted_index >= 25 - '$r'
AND app_id = '$app_id'
ORDER BY adjusted_index DESC --- this line is needed if
"; --- you have a UNIQUE index on
--- (app_id, adjusted_index)
$q2 = " UPDATE app_sign_ups
SET adjusted_index = 25 - '$r'
WHERE adjusted_index = 26
AND app_id = '$app_id'
";