Calculate the total sum for each category from a t

Question

I have a text file there I store all the data like this:

dept|test-1|365|0
dept|test-2|134|0
expense|test-3|4387|0
expense|test-4|105|0

How can I get the sum of each category for all the transaction within that category?

In this example, the category dept will have a total sum of 499 and the category expense will have a total sum of 4'492.

But how can I do this within PHP?

Here's my current code:

function do_maths($expression) {
    eval('$o = ' . preg_replace('/[^0-9\+\-\*\/\(\)\.]/', '', $expression) . ';');
    return $o;
}

function inifile($fname, $word) {
    $contents = file_get_contents($fname.'.ini');
    $pattern = preg_quote($word, '/');
    $pattern = "/^.*$pattern.*\$/m";

    if(preg_match_all($pattern, $contents, $matches)) {
        sort($matches[0]);

        # TABELL
        echo '<div class="table">';

            # LOOP
            foreach($matches[0] AS $found) {
                $transaction = explode('|', $found);

                echo '<div class="table-row'.($transaction[3] == 1 ? ' color-grey-light" title="'.$transaction[1].' är betalad"' : '"').'>';
                    echo '<div class="table-cell-left table-cell-left-width">';
                        echo $transaction[1];
                    echo '</div>';

                    echo '<div class="table-cell-right">';
                        echo '<span class="sum">';
                            echo do_maths($transaction[2]);
                        echo '</span> kr';
                    echo '</div>';
                echo '</div>';
            }

        echo '</div>';


    } else {
        echo '<div class="message color-blue">';
            echo 'Kunde inte hitta något';
        echo '</div>';
    }
}

Answer 1

This should work for you:

First read your text file into an array with file(). Then go through each array element(each line of your text file) and explode() it by | as delimiter.

So your array will change from (Array at point 1):

Array
(
    [0] => dept|test-1|365|0
    [1] => dept|test-2|134|0
    [2] => expense|test-3|4387|0
    [3] => expense|test-4|105|0
)

to (Array at point 2):

Array
(
    [0] => Array
        (
            [0] => dept
            [1] => test-1
            [2] => 365
            [3] => 0
        )
    //...
)

After this you can loop through your $lines array and use the category (first array element of the subArray) as index and add the value (third array element) to the array.

Then you can call array_sum() for each category subArray with array_map().

So you sum all values together for each category. So your array (Array at point 3):

Array
(
    [dept] => Array
        (
            [0] => 365
            [1] => 134
        )

    [expense] => Array
        (
            [0] => 4387
            [1] => 105
        )

)

Will become like this (Array at point 4):

Array
(
    [dept] => 499
    [expense] => 4492
)

And at the end just loop through your array and display your data:

<?php

    $lines = file("test.txt", FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);  //Array at point 1
    $lines = array_map(function($v){
        return explode("|", $v);
    }, $lines);  //Array at point 2

    foreach($lines as $line)
        $data[$line[0]][] = $line[2];
    //Array at point 3  
    $result = array_map("array_sum", $data); 
    //Array at point 4
    foreach($result as $category => $sum)
        echo $category . " = " . $sum . "<br>";

?>

output:

dept = 499
expense = 4492

EDIT:

So my code implemented into your function will look something like this:

<?php

    function getCategoryFromFile($fileName, $category) {
        $lines = file($fileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

        $lines = array_map(function($v){
            return explode("|", $v);
        }, $lines);

        $keys = array_keys(array_column($lines, 0), $category);


        if(!empty($keys)) {

            echo '<div class="table">';

            foreach($keys as $key) {

                echo '<div class="table-row' . ($lines[$key][3] == 1 ? ' color-grey-light" title="' . $lines[$key][1] . ' är betalad"' : '"') . '>';
                    echo '<div class="table-cell-left table-cell-left-width">';
                        echo $lines[$key][1];
                    echo '</div>';                      
                echo '</div>';

            }

            echo '<div class="table-cell-right">';
                echo '<span class="sum">';
                    echo array_sum(array_column(array_intersect_key($lines, array_flip($keys)), 2));
                echo '</span> kr';
            echo '</div>';

            echo '</div>';

        } else {
            echo '<div class="message color-blue">';
                echo 'Kunde inte hitta något';
            echo '</div>';
        }

    }

    //As example
    getCategoryFromFile("test.txt", "expense");

?>

The first part is the same as in my code above. But to now only loop through the data of the category which you want we need the keys to these subArrays. Which we get with this line:

$keys = array_keys(array_column($lines, 0), $category);

So basically we grab all categories from your array with array_column($lines, 0), which will return an array like this:

Array
(
    [0] => dept
    [1] => dept
    [2] => expense
    [3] => expense
)

Then with array_keys() and $category we only get the keys of this array from the category which you want. Which for example for expense will be:

Array
(
    [0] => 2
    [1] => 3
)

Then we just check if we have some keys, means some lines with the category which you want. If yes we loop through these keys and use them to access the data, which you want.

After the foreach loop to print the sum of the values we use this line:

echo array_sum(array_column(array_intersect_key($lines, array_flip($keys)), 2));

But let's break it a bit down. First we have:

array_intersect_key($lines, array_flip($keys))

Where we grab the key intersect of these two arrays with array_intersect_key(). On the left side we have the array $lines as first array which as we know looks something like this:

Array
(
    [0] => Array
        (
            [0] => dept
            [1] => test-1
            [2] => 365
            [3] => 0
        )
    //...
)

Now on the other side as second array we have the keys from the category which we want, so:

Array
(
    [0] => 2
    [1] => 3
)

And then the result/intersect will be (Note: Since we grab the intersect of they keys, we have to flip $keys with array_flip()):

Array
(
    [2] => Array
        (
            [0] => expense
            [1] => test-3
            [2] => 4387
            [3] => 0
        )

    [3] => Array
        (
            [0] => expense
            [1] => test-4
            [2] => 105
            [3] => 0
        )

)

Out of this array we get the second column from each subArray with array_column(). So from the above array we will end up with:

Array
(
    [0] => 4387
    [1] => 105
)

And then finally we get the sum of this array with array_sum() and you will get:

4492