根据总和（值）设置字段

Question

Apologies for ambiguous title

Given the following data..

  +------+------------+--------+
  |  ID  |  value     | used   |
  +------+------------+--------+
  | 1001 |    1       |   0    |
  +------+------------+--------+
  | 1002 |    1       |   0    |
  +------+------------+--------+
  | 1003 |    2       |   0    |
  +------+------------+--------+
  | 1004 |    4       |   0    |
  +------+------------+--------+
  | 1005 |    1       |   0    |
  +------+------------+--------+
  | 1006 |    1       |   0    |
  +------+------------+--------+
  | 1007 |    1       |   0    |
  +------+------------+--------+
  | 1008 |    6       |   0    |
  +------+------------+--------+

I would like to set the sum value of say 8, so the result would be ....

  +------+------------+--------+
  |  ID  |  value     | used   |
  +------+------------+--------+
  | 1001 |    1       |   1    |
  +------+------------+--------+
  | 1002 |    1       |   1    |
  +------+------------+--------+
  | 1003 |    2       |   1    |
  +------+------------+--------+
  | 1004 |    4       |   1    |
  +------+------------+--------+
  | 1005 |    1       |   0    |
  +------+------------+--------+
  | 1006 |    1       |   0    |
  +------+------------+--------+
  | 1007 |    1       |   0    |
  +------+------------+--------+
  | 1008 |    6       |   0    |
  +------+------------+--------+

I would do this my going through each item adding it to a total value, similar to this

  $goal = 8;
  $select = "SELECT ID, value FROM table WHERE !used";
  $result = mysql_query($select);
  while ($row = mysql_fetch_array($result))
     {
     $total = $total + $row['value'];
     if ($total => $goal) 
         { 
         $over = $goal - $total
         if ($over)
            {
            $id = $row['id'];
            mysql_query("INSERT INTO table(value,used) VALUES('$over',0)");         
            mysql_query("update table SET used = 1 WHERE id = '$id'")                 
            }  
          break;
         }
     $id = $row['id'];
     mysql_query("update table SET used = 1 WHERE id = '$id'")  
     }

So, pretty ugly. suggestions of a better method?

Answer 1

Give this a whirl:

      $goal = 8;
      $used = Array()
      $select = "SELECT ID, value FROM table WHERE !used";
      $result = mysql_query($select);
      while ($row = mysql_fetch_array($result))
         {
         $total = $total + $row['value'];
         $used[] = $row['ID'];
         // Check for exact march
         if ($total == $goal) {
             // Loop through all used ID's to get an exact match
             foreach($used as $usedId) {
                mysql_query("update table SET used = 1 WHERE id = " . $usedId);                 
             }
             break;
         } elseif ($total > $goal) { // If over then insert as a new record
             $leftOver = $total - $goal;
             mysql_query("INSERT INTO table(value,used) VALUES($leftOver, 0)");         
             break;
         }
      }

What this doesn't answer is if the total is less than your goal - what happens then?