Set field based on sum(value)

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?

You'll get [told off](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — George, Feb 25 '13 at 08:47
Besides "don't use `mysql_*` anymore"...around how large is your table? — Passerby, Feb 25 '13 at 08:53
why was this down voted, it shows some research, seems clear to me. — maxum, Feb 25 '13 at 09:09
`1001 -> 1004` would be set to `1`, `1` would be inserted back in the DB with a new `ID` — maxum, Feb 25 '13 at 09:19

SaRiD · Accepted Answer · 2013-02-25T09:45:45.787

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?

edited Feb 25 '13 at 09:45

answered Feb 25 '13 at 09:02

SaRiD

1,020
11
15

yep, but if it is not possible to get the exact value the remainder is inserted back into the DB – maxum Feb 25 '13 at 09:05
So if $goal = 5 in your example above it wouldn't match because the total would go from 4 to 8. Would 4 then get inserted into the table above? – SaRiD Feb 25 '13 at 09:11
Right ok, and 3 would get inserted into the table with ID 1009? – SaRiD Feb 25 '13 at 09:18
it is already predefined that total cannot be less than the goal. – maxum Feb 25 '13 at 09:51
Ok then - does this help solve your problem then or does it require more tweaks? – SaRiD Feb 25 '13 at 10:00

score 0 · Answer 2 · answered Feb 25 '13 at 09:24

0

Try this query -

SELECT t1.ID, t1.value, IF(SUM(t2.value) > 8, 0, 1) used FROM used t1
  JOIN used t2
    ON t1.ID >= t2.ID
  GROUP BY t1.ID

answered Feb 25 '13 at 09:24

Devart

119,203
23
166
186

Can I have some explanation in what is going on here? – maxum Feb 25 '13 at 09:27
This query will count SUM of values and check `used` field where SUM less then 9. If this query is OK, then rewrite it to UPDATE query. – Devart Feb 25 '13 at 09:30

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

First of all you should not use old and deprecated mysql_* functions. Use MySQLi or PDO instead.

$goal=13;
$cache=0;
$idCache=0;
$result=$mysqli->query("SELECT id,value FROM [table] WHERE used=0 ORDER BY id ASC");
while($row=$result->fetch_assoc())
{
    $idCache=intval($row["id"]);
    $cache+=intval($row["value"]);
    if($cache>=$goal) break;
}
$result->free();
$stmt=$mysqli->prepare("UPDATE [table] SET used=1 WHERE used=0 AND id<=?");
$stmt->bind_param("i",$idCache);
$stmt->execute();
$stmt->close();
if($cache>$goal)
{
    $stmt=$mysqli->prepare("INSERT INTO [table] (value,used) VALUES (?,0)";
    $cache=$goal-$cache;
    $stmt->bind_param("i",$cache);
    $stmt->execute();
    $stmt->close();
}

Explanation:

From your demo data I assumed that you want to fetch your data sorted by id ascending, so I query them all with an ORDER BY clause;

(the $mysqli->query part)
Sum the result into $cache until it gets not less than $goal;

(the while part)
Since we get the value in id-ascending order, we can safely mark all IDs not greater than the last row we get to be "used";

(the $mysqli->prepare before if block)
If the sum is greater than goal, we added the "overflowed" value back into the table, with used marked as 0, as you hinted in your code.

(the if block)

Note that the above code does not take the "what if not enough value to sum up $goal" situation into account. You'll have to deal with that if it can be a real case.

Set field based on sum(value)

3 Answers3