Keep subtracting value in loop getting mysql result php

Question

I have an mysql table named example.

 Id   | Amount  | Left | Filled
 1    |  1      |  1   | 0
 2    |  4      |  4   | 0
 5    |  7      |  7   | 0

I have an variable named $var = 9 Now I have an array named $array with those ids as array([0] => 1, [1] => 2, [2] => 5) Which itself is a mysql result. How do I make a loop so that ids in array keep subtracting the left and keep filling as per the amount but within the total value of $var so that my end result in table is

 Id   | Amount  | Left | Filled
 1    |  1      |  0   | 1
 2    |  4      |  0   | 4
 5    |  7      |  3   | 4

How come `5` isn't `0` and `7` for `Left` and `Filled`? I don't understand the logic. — JNevill, Nov 05 '18 at 15:55
As the amount asked is 9 in `$var` So it needs to keep looping and subtracting till value 0 in ascending order. So at last for ID 5 filled is 4 — Zooocc Refback, Nov 05 '18 at 15:59

dWinder · Answer 1 · 2018-11-05T20:18:09.533

0

You can use while loop in order to loop on the ids and reduce the amount in each iteration.

I am not sure how you access your DB so I leave it pseudo.

Consider the following code:

$ids = array(1,2,5);
$value = 9;

function reduceAmount($id, $value) {
    $query = mysqli_query($conn, "SELECT * FROM example WHERE Id='$id'");
    $row = mysqli_fetch_array($query);     
    $take = min($row['Left'], $value); // the amount you can take (not more then what left)

    $left = $row['Left'] - $take;
    $filled = $row['Filled'] + $take;
    $conn->query("UPDATE example SET Left='$left', Filled='$filled' WHERE Id='$id'")
    return max(0, $value - $take);
}

while ($value > 0 && !empty($ids)) { // check if value still high and the options ids not finish
    $id = array_shift($ids); //get first ID
    $value = reduceAmount($id, $value);
}

You can check at the end of the loop if value still bigger then 0 - this can happen when no enough "Amount" in ids

edited Nov 05 '18 at 20:18

answered Nov 05 '18 at 16:11

dWinder

11,597
3
24
39

How do you fetch element from DB? with `mysql_query`? `mysql_fetch_array`? – dWinder Nov 05 '18 at 16:27
mysql_query? Crikey, let's hopes not! – Strawberry Nov 05 '18 at 16:58
mysqli I mean . – Zooocc Refback Nov 05 '18 at 17:17
Please give me example of how you fetch something from you DB – dWinder Nov 05 '18 at 17:25
`$query = mysqli_query($conn, $sql); while($rows = mysqli_fetch_array($query)){ $gg[] = $rows[0]; }` – Zooocc Refback Nov 05 '18 at 17:27
@ZoooccRefback Updated with `mysqli_query` – dWinder Nov 05 '18 at 18:06
No, I found my own way : ) – Zooocc Refback Nov 08 '18 at 08:25
@ZoooccRefback If so, please post it here as answer so other may learn from it – dWinder Nov 08 '18 at 08:29

Keep subtracting value in loop getting mysql result php

1 Answers1

Linked