Putting values into an array and using array_sum

Question

ok im a little stuck, i know its a simple thing im missing here so hoping fresh eyes will help

I have values in a column stored as 2:7:99 etc each value is seperated by :

Now I can seperate all the values out and query another table to get the price which corresponds to that value.

The issue i'm having is doing a SUM of all the price values See code below I think the easiest way would be to add all the price values into an array and then do array_sum() but for some reason i just cant get it working

** Please DO NOT Mention SQL Injection .. Its on a LOCAL machine with NO outside access and only myself will be using this

    <?php
include('config.php');
// Function for calculation Rough Invoice Total
function basicTotal() {
    $con = mysqli_connect("localhost","USER","PASS","TABLE");
    $wtbdq = mysqli_query($con,"SELECT * FROM `jobs` WHERE wsjid = '18'");
    $wtbdr = mysqli_fetch_assoc($wtbdq);
    do {
    $wtbd = explode(":",$wtbdr['worktobedone']);
    foreach($wtbd as $item) 
        {
            $priceq = mysqli_query($con,"SELECT * FROM `workshop-items` WHERE wsiid = '$item'");
            $pricer = mysqli_fetch_assoc($priceq);

            $price = array($pricer['incvat']);

            echo $item.' - '. $pricer['incvat'].'<br>';

        }

    } while($wtbdr = mysqli_fetch_assoc($wtbdq));

    $total = array_sum($price);
    echo $total;
}
basicTotal();
?>

Thanks in advance

How about `$total = 0; foreach(...) {... $total += $pricer['incvat']; ...}`? — Ruslan Osmanov, May 17 '17 at 12:46
Why not use SELECT *, SUM(incvat) AS total FROM `workshop-items` WHERE wsiid = '$item' ? — Gerard, May 17 '17 at 12:56

score 4 · Answer 1 · answered May 17 '17 at 12:46

4

just replace  $price = $pricer['incvat']; 

with this in your code

$price[] = $pricer['incvat'];

answered May 17 '17 at 12:46

Nithin John

897
3
8
21

score 2 · Answer 2 · answered May 17 '17 at 12:43

The issue with your current approach is you are overwriting the $price variable. You need to push the value in the $price array like $price[] = $pricer['incvat']. You need to do this

    <?php
include('config.php');
// Function for calculation Rough Invoice Total
function basicTotal() {
    $con = mysqli_connect("localhost","USER","PASS","TABLE");
    $wtbdq = mysqli_query($con,"SELECT * FROM `jobs` WHERE wsjid = '18'");
    $wtbdr = mysqli_fetch_assoc($wtbdq);
    do {
    $wtbd = explode(":",$wtbdr['worktobedone']);
    foreach($wtbd as $item) 
        {
            $priceq = mysqli_query($con,"SELECT * FROM `workshop-items` WHERE wsiid = '$item'");
            $pricer = mysqli_fetch_assoc($priceq);

            $price[] = $pricer['incvat'];

            echo $item.' - '. $pricer['incvat'].'<br>';

        }

    } while($wtbdr = mysqli_fetch_assoc($wtbdq));

    $total = array_sum($price);
    echo $total;
}
basicTotal();
?>

score 2 · Accepted Answer · answered May 17 '17 at 12:43

2

You are all the time overwriting you final price:

$price = array($pricer['incvat']);

Replace that with:

$price[] = $pricer['incvat'];

answered May 17 '17 at 12:43

matiaslauriti

7,065
4
31
43

Any idea why its adding up the values of all the records and not just the current row – Chris Yates May 17 '17 at 14:18
It should be adding only the ones that comes from `SELECT * FROM jobs WHERE wsjid = '18'`. Debug that this query is not returning all IDs you are then using to get the prices. But you can explain a little more about what is exactly happening now. I think you should open a new question. – matiaslauriti May 17 '17 at 14:25
1

Hi ive raised a new question showng all code http://stackoverflow.com/questions/44028136/array-sum-counting-all-records-in-result-query-not-each-row-individually – Chris Yates May 17 '17 at 14:47

Putting values into an array and using array_sum

3 Answers3