MySQL - best way to update multiple rows based on text sent/fail

Question

I have a PHP script that runs daily, loops through all users to send them a text, and updates their status. It's basically like this...(pseudo-code)

$sql="SELECT UserPhone from users WHERE Status='LOW'";
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) {
   // loop through and send texts
}
// then update status
$sql="UPDATE users SET Status='MED'";

I can detect whether the text was sent successfully, and I'd like to only update the user's status if the text was successful. My first instinct is to move the UPDATE into the while loop and update each user's row individually, something like this, pseudo-code again:

while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) {
    $sent=sendText($phone);
    if($sent) {
        $sql=UPDATE users SET Status='MED' WHERE UserPhone=$phone";
    }
}

This feels clunky to me and I'm guessing isn't good for performance. It feels like I should be able to store/remember which texts sent and update all those users with a single UPDATE (or at least something that doesn't require a new UPDATE for every row).

I know just enough MySQL to do dumb stuff with it. Is there a better way to do this?

You can avoid the multiple single updates by remembering the phone numbers in an array and then call a single update with an `IN` clause at the end. Maybe this helps: https://stackoverflow.com/questions/17870999/bind-multiple-parameters-into-mysqli-query — Thorsten Kettner, May 31 '20 at 16:09

score 1 · Answer 1 · answered May 31 '20 at 16:11

You could do something like this:

$sql="SELECT UserPhone from users WHERE Status='LOW'";
$successes = []
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) {
   // loop through and send texts, set $ok to the success/fail
   if ($ok) {
      $successes[] = $row['UserPhone']
   }
}
// then update status
$placeholders = implode(",", array_fill(0, count($successes), "?");
$sql="UPDATE users SET Status='MED' WHERE UserPhone IN ($placeholders)";
$stmt = mysqli_prepare($sql);
$stmt->bind_param(str_repeat("s", count($successes)), ...$successes);
$stmt->execute();

But this is a risk. What if the PHP script is interrupted partway through? For example, during the while loop, perhaps one of the text-sendings causes a fatal error and the script exits, losing the information accumulated so far about which texts succeeded.

I worked for a couple of years at SchoolMessenger.com. We sent up to 9 million texts per day (it may be higher now since I left that job).

Here's the pseudocode for how we did the task (our service was written in Java, not PHP):

for each user who should be sent a text {
  INSERT INTO Tasks SET UserPhone = ?, Message = ?, Status = 'TODO'
}

Then we have one row per user per text to send. There's another program monitoring this Tasks table, looking for any tasks with status of 'TODO'. When this finds that some rows exist:

for each row in Tasks that is 'TODO' {
  Send the text message
  Check success
  Update the status on that one row to 'Success' or 'Fail'
}

This seems clunky, but a lot of systems like this are designed to remember where they left off if they crash. Saving the result to the database one step at a time helps, because once the program starts up, it can see which texts were already sent, and avoid re-sending any texts (because parents of school children would not like to receive the same message repeatedly).

MySQL - best way to update multiple rows based on text sent/fail

1 Answers1