Adding up the digits in a number

Databases
1

Is it possible to turn this php script into a mysql UPDATE query (minus the echo)? Where a row would be updated with the value in $total.

<?php
$str = 12345678901;
$total = 0;
while (strlen($str) > 0) {
  $total = $total + substr($str,0,1);
  $str = substr($str,1);
}  
$total = 10 - fmod($total,10);
echo $total . '</br>';
?>

Otherwise, I’ll just do a series of SELECT/UPDATEs using this script.

2

How do you add up the sum of the digits in the number 12345678901 with sql?

3

could you please explain the application requirement here?

what’s this really needed for?

“add up the sum of the digits in the number” does not sound like any type of business situation

4

It’s for a check digit for a postnet bar code:http://en.wikipedia.org/wiki/POSTNET

I can accomplish the task with a while loop in php, but haven’t ever used a while loop in mysql (thought this is a good opportunity) . I’ve read the reference http://dev.mysql.com/doc/refman/5.0/en/while-statement.html ,but don’t understand how to get the results of the while loop into the field in a UPDATE query.

How does that work with a while loop?

SET chk_digit = …

5

That sort of calculation would be far more efficiently done outside of the database. Validating the checkdigit would be something you’d do as part of the validation you do long before the field ever gets anywhere near the code that adds it into the database in the first place. Once the data is in the database the checkdigit should always be valid as invalid ones should never be inserted to start with. Sanitising the value after you retrieve it if you want to make sure no one has tampered with the database content would also be best done after you get the field back out of the database.

You should use each language for what it is best at and PHP is far more efficient at those sort of calculations than any database would be.

6

Thanks for your help r937 and felgall. That’s a load off my mind.