英文:
subtract one from all rows every minute - sql php
问题
我有一个包含已购买计划的数据库，其中包含了剩余分钟数的数值，我想要每分钟减去1个数字。
现在我正在使用以下代码，并通过cron作业执行：
$sql = "SELECT * FROM pls";
$stmt = $pdo->query($sql);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$theM = $row['minutes_left'];
$realM = intval($theM) - 1;
$sqlE = "UPDATE pls SET minutes_left=?";
$stmtE = $pdo->prepare($sqlE);
$stmtE->execute([strval($realM)]);
}
但是，例如，如果一个计划有27分钟，另一个有12分钟，还有一个有15分钟，它们都会变成26、25等，而它们各自不会减少。
英文:
I have a database of a number of purchased plans with a value of minutes_left from which I want to subtract 1 number every minute.
Now I am using this code using cron job:
$sql = "SELECT * FROM pls";
$stmt = $pdo->query($sql);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$theM = $row['minutes_left'];
$realM = intval($theM) - 1;
$sqlE = "UPDATE pls SET minutes_left=?";
$stmtE = $pdo->prepare($sqlE);
$stmtE->execute([strval($realM)]);
}
But for example, if there are 27 minutes from one, and 12 minutes from another, and 15 minutes from another, all of them will change to 26, 25, etc., and each of them will not reduce theirs.
答案1
得分: 1
UPDATE pls SET minutes_left=? 应该改成类似 UPDATE pls SET minutes_left=minutes_left-? 或者甚至 UPDATE pls SET minutes_left=minutes_left-1 WHERE minutes_left > 0。这意味着，你不想将 minutes left 更新为一个字面值，而是想从中减去下一分钟的值 - 一个计算出的值。
但是考虑设置类似于开始时间和分钟或过期时间，然后你可以只检查是否超过了过期时间，而无需不断更新（这与生日的概念相同 - 你是否在数据库中保存年龄，还是保存生日。年龄始终在变化，但生日是一个你可以用来计算年龄的常量）。
英文:
UPDATE pls SET minutes_left=? should be something like UPDATE pls SET minutes_left=minutes_left-? or even UPDATE pls SET minutes_left=minutes_left-1 where minutes_left > 0 Meaning, you don't want update minutes left to a literal value, but instead you want to update minutes_left to subtract the next minute from it - a calculated value.
But consider setting something like a start time and minutes or an expiration time and then you could just check if expiration time is exceeded without constant updates (this is the same concept as birthdays - do you save Age in your database, or do you save BirthDate in your database. Age is always changing, but BirthDate is a constant you can use to calculate Age.
答案2
得分: 1
不需要先查询它们，然后再更新。您可以在单个步骤中进行更新。
UPDATE pls SET minutes_left = minutes_left - 1
如果您不希望低于0，可以使用名为GREATEST()的最大函数。
UPDATE pls SET minutes_left = GREATEST(minutes_left - 1, 0)
或者由@topsail建议，仅减少大于0的那些。
UPDATE pls SET minutes_left = minutes_left - 1 WHERE minutes_left > 0
英文:
There is no need to query them first and then update. You can do the update in a single step.
UPDATE pls SET minutes_left = minutes_left - 1
If you don't want to go below 0 you can use a max function called GREATEST().
UPDATE pls SET minutes_left = GREATEST(minutes_left - 1, 0)
or suggested by @topsail to decrease only those which are greater than 0.
UPDATE pls SET minutes_left = minutes_left - 1 WHERE minutes_left > 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论