Building a "crosstab" or "pivot" table from an array in php

Question

I have an array of objects defined similarly to the below:

$scores = array();

// Bob round 1
$s = new RoundScore();
$s->Round_Name = 'Round 1';
$s->Player_Name = 'Bob';
$s->Score = 10;
$scores[0] = $s;

// Bob round 2
$s = new RoundScore();
$s->Round_Name = 'Round 2';
$s->Player_Name = 'Bob';
$s->Score = 7;
$scores[1] = $s;

// Jack round 1
$s = new RoundScore();
$s->Round_Name = 'Round 1';
$s->Player_Name = 'Jack';
$s->Score = 6;
$scores[2] = $s;

// Jack round 2
$s = new RoundScore();
$s->Round_Name = 'Round 2';
$s->Player_Name = 'Jack';
$s->Score = 12;
$scores[3] = $s;

If I loop through and dump the $scores object into a table, it will look something like this:

Round_Name   Player   Score
----------------------------
Round 1      Bob        10
Round 2      Bob         7
Round 1      Jack        6
Round 2      Jack       12

What I want, however, is something like this:

Player  Round 1  Round 2  Total
-------------------------------
Bob       10        7       17
Jack       6       12       18

I'm not going to know in advance how many rounds or players there'll be and let's just say I can't change the way the objects are constructed.

What's the most efficient way to do this in php?

Solution 1

If we can assume that:

• the order of scores in the array is always in the order by player's name and then by the round number
• the number of rounds is same for each player

Then, what we can do is print each player's score as we moved through the array while calculating the total in the process but resetting it if we see a new player:

$round_count = 0;
$header_printed = false;
$current_player = NULL;
$current_total = 0;
$current_output_line = "";
foreach ($scores as $score) {
    // Check whether we have to move to a new player
    if ($score->Player_Name != $current_player) {
        // Check whether we have anything to print before
        // resetting the variables
        if (!is_null($current_player)) {
            if (!$header_printed) {
                printf("%-10s", "Player");
                for ($i = 0; $i < $round_count; $i++) {
                    printf("%-10s", "Round $i");
                }
                printf("%-10s\n", "Total");

                $header_printed = true;
            }

            $current_output_line .= sprintf("%5d\n", $current_total);
            print $current_output_line;
        }

        // Reset the total and various variables for the new player
        $round_count = 0;
        $current_player = $score->Player_Name;
        $current_total = 0;
        $current_output_line = sprintf("%-10s", $score->Player_Name);
    }

    $round_count++;
    $current_total += $score->Score;
    $current_output_line .= sprintf("%5d     ", $score->Score);
}
// The last player is not printed because we exited the loop 
// before the print statement, so we need a print statement here.
if ($current_output_line != "") {
    $current_output_line .= sprintf("%5d\n", $current_total);
    print $current_output_line;
}

Sample output:

Player    Round 0   Round 1   Total
Bob          10         7        17
Jack          6        12        18

This should be quite efficient because it only goes through the array once.

Solution 2

As far as I can tell, PHP arrays are implemented as hash tables (so lookup/update should be pretty efficient) Will time efficiency even be a problem, anyway?

I would just do it the "simple" way:

$table = array();
$round_names = array();
$total = array();

foreach ($scores as $score)
{
    $round_names[] = $score->Round_Name;
    $table[$score->Player_Name][$score->Round_Name] = $score->score;
    $total[$score->Player_Name] += $score->score;
}

$round_names = array_unique($round_names);

foreach ($table as $player => $rounds)
{
    echo "$player\t";
    foreach ($round_names as $round)
        echo "$rounds[$round]\t";
    echo "$total[$player]\n";
}

(I know the arrays aren't properly initialized, but you get the idea)

Comments

[Zecc]

I've upvoted this answer rather than maxyfc's because it is much simpler and robust. I really don't see performance being a bottleneck here; code maintainability is more important. You'll probably also want to do a natsort($round_names) btw.

[sanu]

Why you used foreach ($round_names as $round) echo rounds[$round]\t"; instead of foreach ($rounds as $round) echo $round