Question 173845 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – subtract one from all rows every minute – sql php

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search