how to create a pivot array in php

Question

I have the following :

   $data = [
  {model_num : "ABC", revision : "AA", "testRecipeID":85, value : 31.25, treatment : 'Pressure' },
  {model_num : "ABC", revision : "AA", "testRecipeID":85, value : 31.25, treatment : 'Gas' },
  {model_num : "ABC", revision : "AA", "testRecipeID":85, value : 33.12, treatment : 'Temp' },
  {model_num : "ABC", revision : "AA", "testRecipeID":85, value : 25.87, treatment : 'Current' },
  {model_num : "ABC", revision : "AB", "testRecipeID":86, value : 26.63, treatment : 'Pressure' },
  {model_num : "ABC", revision : "AB", "testRecipeID":86, value : 26.00, treatment : 'Gas' },
  {model_num : "ABC", revision : "AB", "testRecipeID":86, value : 23.75, treatment : 'Temp' }
];

and i would like to end up with something like this:

var data=[{model_num : "ABC", revision : "AA", "testRecipeID":85, "Pressure":31.25, "Gas":31.25, "Temp": 33.12,"Current":25.87 },{model_num : "ABC", revision : "AB", "testRecipeID":86, "Gas":26.00,"Temp":23.75}]

I know how to do this in JS but not on PHP and it turns out I need to do it in my php so that I can process the large amounts of data that I have. I have this based on another question that I found but It doesn't work it returns a 0

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

foreach ($data as $score)
{
    $round_names[] = $score->treatment;
    $table[$score->testRecipeID][$score->treatment] = $score->value;
    $total[$score->testRecipeID] += $score->value;
    print_r($round_names);

}


$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";
}

any help will be greatly appreciated!

this is how i do it in JS

var result = [];
data.forEach(function(e) {
  var a = e.model_num + '|' + e.revision+ '|'e.recipeID;
   if(!this[a]) {
    this[a] = {model_num: e.model_num, revision: e.revision, recipeID: e.recipeID}
    result.push(this[a]);
  }
  this[a][e.treatment] = e.value;
}, {});

You're saying yo know how to do this in JS - tell us, converting functionality to PHP should be no problem — mazedlx, Sep 01 '16 at 17:26
Are you guaranteed that input `$data` array is sorted by revision or testRecipeId (which seem to be redundant in terms of their uniqueness)? — Mike Brant, Sep 01 '16 at 17:50
@MikeBrant yeah so all i need is to sort them by unique recipeId there won't be duplicates so as long as I can pivot all the treatments and values based on recipeID that should do it. — cocopan, Sep 01 '16 at 17:54

score 1 · Answer 1 · answered Sep 01 '16 at 17:11

1

If you need the structure, you can try with JSON Encode:

<?php
  $arr = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);

  echo json_encode($arr);
?>

Which outputs:

{"a":1,"b":2,"c":3,"d":4,"e":5}

If you need it to be an array, use:

echo json_encode(array($arr));

answered Sep 01 '16 at 17:11

Rodrigo Mata

1,779
2
14
23

thanks I actually need to be able to combine the records in a pivot array meaning, instead of having 3 lines for test recipe ID 85 I would like to have only one with all its treatment and values – cocopan Sep 01 '16 at 17:16
Sorry if I misunderstood your question, now I get the point; so, do you send the var data from JS and you want it to mix it in PHP? Or do you want to send it all in one line from your JS? – Rodrigo Mata Sep 01 '16 at 17:31
no worries thanks for trying to help I appreciate it. the var data is just a simplified version of a MSSQL result that I get , then I was comparing that result in php and sending the compared result to JS and creating the PIVOT in JS. The problem with this approach is that I have a lot of data and comparison killing processing time and never making it to my JS, so I want to pivot in PHP after i get query results, and then compare that and just send it to js already pivoted – cocopan Sep 01 '16 at 17:38

score 0 · Accepted Answer · answered Sep 02 '16 at 05:59

This is your JS function in PHP

<?php
$data = '[
{"model_num" : "ABC", "revision" : "AA", "testRecipeID":85, "value" : 31.25, "treatment" : "Pressure" },
{"model_num" : "ABC", "revision" : "AA", "testRecipeID":85, "value" : 31.25, "treatment" : "Gas" },
{"model_num" : "ABC", "revision" : "AA", "testRecipeID":85, "value" : 33.12, "treatment" : "Temp" },
{"model_num" : "ABC", "revision" : "AA", "testRecipeID":85, "value" : 25.87, "treatment" : "Current" },
{"model_num" : "ABC", "revision" : "AB", "testRecipeID":86, "value" : 26.63, "treatment" : "Pressure" },
{"model_num" : "ABC", "revision" : "AB", "testRecipeID":86, "value" : 26.00, "treatment" : "Gas" },
{"model_num" : "ABC", "revision" : "AB", "testRecipeID":86, "value" : 23.75, "treatment" : "Temp" }
]';

$data = json_decode($data);
$result = [];
foreach ($data as $row) {
    $a = $row->model_num . '|' . $row->revision . '|' . $row->testRecipeID;
    if (! array_key_exists($a, $result)) {
        $result[$a] = [
            'model_num' => $row->model_num,
            'revision' => $row->revision,
            'testRecipeID' => $row->testRecipeID
        ];
    }
}

score 0 · Answer 3 · answered Apr 09 '22 at 11:14

Because neither of the previous answers managed to provide the desired output, I am compelled to answer this pivot question.

Create a temporary string from the 3 identifying column values.
Use that string as the first level key while grouping related row data.
The null coalescing assignment operator spares you needing to call isset() to check if the group has been encountered before. If it is the first encounter, save the three core elements to the row.
Unconditionally push the dynamically keyed value into the group's row. 5 When finished iterating clear away the temporary keys with array_values().

Code: (Demo)

$data = [
    ['model_num' => 'ABC', 'revision' => 'AA', 'testRecipeID' => 85, 'value' => 31.25, 'treatment' => 'Pressure'],
    ['model_num' => 'ABC', 'revision' => 'AA', 'testRecipeID' => 85, 'value' => 31.25, 'treatment' => 'Gas'],
    ['model_num' => 'ABC', 'revision' => 'AA', 'testRecipeID' => 85, 'value' => 33.12, 'treatment' => 'Temp'],
    ['model_num' => 'ABC', 'revision' => 'AA', 'testRecipeID' => 85, 'value' => 25.87, 'treatment' => 'Current'],
    ['model_num' => 'ABC', 'revision' => 'AB', 'testRecipeID' => 86, 'value' => 26.63, 'treatment' => 'Pressure'],
    ['model_num' => 'ABC', 'revision' => 'AB', 'testRecipeID' => 86, 'value' => 26.0,  'treatment' => 'Gas'],
    ['model_num' => 'ABC', 'revision' => 'AB', 'testRecipeID' => 86, 'value' => 23.75, 'treatment' => 'Temp']
];
$result = [];
foreach ($data as $row) {
    $compositeKey = "{$row['model_num']}-{$row['revision']}-{$row['testRecipeID']}";
    $result[$compositeKey] ??= [
        'model_num' => $row['model_num'],
        'revision' => $row['revision'],
        'testRecipeID' => $row['testRecipeID']
    ];
    $result[$compositeKey][$row['treatment']] = $row['value'];
}
var_export(array_values($result));

Output:

array (
  0 => 
  array (
    'model_num' => 'ABC',
    'revision' => 'AA',
    'testRecipeID' => 85,
    'Pressure' => 31.25,
    'Gas' => 31.25,
    'Temp' => 33.12,
    'Current' => 25.87,
  ),
  1 => 
  array (
    'model_num' => 'ABC',
    'revision' => 'AB',
    'testRecipeID' => 86,
    'Pressure' => 26.63,
    'Gas' => 26.0,
    'Temp' => 23.75,
  ),
)

how to create a pivot array in php

3 Answers3

Linked

Related