How to aggregate (sum) rows in a CSV file and print as a table using PHP

Question

I am trying to use PHP to interpret a simple CSV log file and print it out as a table.

The file has three columns - a name, followed by two columns with a 1 or a 0 in either.

E.g.

Test,1,0
Test,0,1
Test2,1,0
Test3,1,0
Test3,0,1
Test3,1,0
Test3,0,1

The goal is to sum all identical rows together to give this:

Test,1,1
Test2,1,0
Test3,2,2

And lastly print this as an HTML table.

So far I have a working solution for summing the first two columns but I don't know how to get it to work to include the third. To outline the entire process, I have an initial PHP script at the start of a web page that logs clicks to a CSV file:

<?PHP
if (isset($_GET['s1'])){
$sub1 = urlencode($_GET['s1']);
$fp1 = fopen ('botlog.csv', 'a+' );
fputcsv ( $fp1, array ( $sub1, '1', '0' ), ",", '"' );
fclose ( $fp1 );}
?>

Later I have a second script, loaded in an iFrame with a JS delay, that logs a similar value but to the third column rather than the second:

<?PHP
if (isset($_GET['sub1'])){
$sub2 = urlencode($_GET['sub1']);
$fp2 = fopen ('botlog.csv', 'a+' );
fputcsv ( $fp2, array ( $sub2, '0', '1' ), ",", '"' );
fclose ( $fp2 );}
?>

Then, I have the following to a) aggregate rows for the 2nd column value (haven't figured out how to do the third too) and put into an array, b) dump it all as a table:

<?php
$array = array_map('str_getcsv', file('botlog.csv'));
$inputfile  = 'botlog.csv';
$inputHandle = fopen($inputfile, "r");
$sumArray = array();
while (($dataRow = fgetcsv($inputHandle, 1000, ",")) !== FALSE) {
    $subid = $dataRow[0];
    $extra1 =  $dataRow[2];
    if (!isset($sumArray[$subid])) {
        $sumArray[$subid] = 0;
    }
 $sumArray[$subid] += $extra1;
}

var_dump($sumArray); //test sum works

function build_table($sumArray){
    // start table
    $html = '<table>';
    // header row
    $html .= '<tr>';
    $header=array("SUBID"=>"1","Initial Clicks"=>"2","Secondary Clicks"=>"3", "Percentage"=>"4");
    foreach($header as $key=>$value){
            $html .= '<th>' . $key . '</th>';
        }
    $html .= '</tr>';
    // data rows
    foreach( $sumArray as $key=>$value){
        $html .= '<tr>';
        foreach($value as $key2=>$value2){
            $html .= '<td>' . $value2 . '</td>';
        }
        $html .= '</tr>';
    }
    // finish table and return it
    $html .= '</table>';
    return $html;
}
echo build_table($array);
?>

The initial code works in that it makes an array with the column 2 values summed. Woot! However, I then try to use the HTML table print out on this sumArray, but it just displays the original content (i.e. not that generated from the while function.

So, goals:

1. Modify initial code block to create a $sumArray that merges all identical column 1 rows but adds their column 2 and 3 values.

2. Print this out in a nifty table with a 4th spare column.

Help much appreciated!

EDIT: This is the final working code I used:

<?php
if (file_exists('botlog.csv')) {
$array = array_map('str_getcsv', file('botlog.csv'));
$inputfile  = 'botlog.csv';
$inputHandle = fopen($inputfile, "r");
$sumArray = array();
while (($dataRow = fgetcsv($inputHandle, 1000, ",")) !== FALSE) {
    $subid = $dataRow[0];
    if (!isset($sumArray[$subid])) {
        $sumArray[$subid] = array_fill(0, count($dataRow)-1, 0);
    }
    for ($i = 1; $i < count($dataRow); $i++) {
    $sumArray[$subid][$i-1] += $dataRow[$i];
}}

arsort($sumArray);
$table = $sumArray;

echo '<table>';
echo "<thead><td><span>Subid</span></td><td><span>Initial Clicks</span></td><td><span>Secondary Clicks</span></td><td><span>Percentage</span></td></thead>";

foreach ($table as $subids => $values)
{
    echo "<tr><td>".$subids."\n";
    echo "<td>" . $values['0'] . "</td>";
    echo "<td>" . $values['1'] . "</td>";
    echo "<td>Final column contents</td>";
}   

echo "</table>";
}
else{ echo "Botlog.csv file was not found in current directory";}
?>

Answer 1

Make $sumArray a 2-dimensional array. The key of the first dimension is the first column of the CSV, and the second dimension is the sums of the remaining columns.

while (($dataRow = fgetcsv($inputHandle, 1000, ",")) !== FALSE) {
    $subid = $dataRow[0];
    if (!isset($sumArray[$subid])) {
        $sumArray[$subid] = array_fill(0, count($dataRow)-1, 0);
    }
    for ($i = 1; $i < count($dataRow); $i++) {
    $sumArray[$subid][$i-1] += $dataRow[$i];
}