my JSON data pattern is as follows:
{
"Contestants": [
{
"Province": "Ontario",
"Classification": "LSPI,,",
"ClassificationDate": "2021",
"RegistrationStatus": "Registered",
"FirstName": "Kyle",
"LastName": "Straunf",
"Gender": "M",
"AGE": null,
"DOB": "02/08/2003",
"Clubs": [
{
"Clubname": "Penguins",
"Code": "MPNO",
"Clubid": "200"
}
],
"Email": null,
"Language": "E",
"ChallengeData": null
},
{
"Province": "Alberta",
"Classification": "LSPI,,",
"ClassificationDate": "2021",
"RegistrationStatus": "Registered",
"FirstName": "Alexander",
"LastName": "Kentwood",
"Gender": "M",
"AGE": null,
"DOB": "08/16/2005",
"Clubs": [
{
"Clubname": "Elegant Dolphins",
"Code": "ZGIA",
"Clubid": "300"
}
],
"Email": null,
"Language": "E",
"ChallengeData": null
}
]
}
I managed to get the data like below, it prints but I'm having trouble printing each csv lines headers and data without it being overwritten. What's a good way to output it into a csv file with the keys as columns headers.
$all_data = json_decode($json_stream, true);
foreach ($all_data as $record) {
foreach ($record as $rec) {
foreach ($rec as $key => $value) {
if (is_array($value)) {
foreach ($value as $value2) {
foreach ($value2 as $key2 => $value2) {
echo "<p> key: $key2 . value: $value2</p>";
}
}
} else {
echo "<p> key: $key . value: $value</p>";
}
}
}
}
As for the expected output, I'd like all the keys to be column headers like the picture below but continued with the other headers and so on.
Classification section:
At the top is how it looks like presently however below is how it would be better
CodePudding user response：
- First step is to get the maximum no. of clubs. This is important to maintain the order of the column headers.
Snippet:
<?php
function getMaxClubs($data){
$max_clubs = 0;
foreach($data as $value){
$max_clubs = max($max_clubs, count($value['Clubs']));
}
return $max_clubs;
}
- Second is to get the column headers. In here, we only proceed to the keys after the
Clubskey once we have processed all the club keys which has maximum no. of clubs. We do this to maintain the order of the keys, a.k.a column headers.
Snippet:
<?php
function getHeadersRow($data, $max_clubs){
$columns = [];
foreach($data as $value){
foreach($value as $k => $v){
if($k === 'Classification'){
$columns['SM'] = $columns['SB'] = $columns['S'] = true;
}elseif($k === 'Clubs'){
if(count($v) !== $max_clubs) break;
$max_clubs = -1; // indicating club keys are processed to avoid repetition
$cnt = 0;
foreach($v as $club_data){
$cnt ;
foreach($club_data as $club_key => $club_value){
$columns[ $club_key. $cnt] = true;
}
}
}else{
$columns[ $k ] = true;
}
}
}
return array_keys($columns);
}
- Now, we loop row by row. We append additional empty row entries for
Clubsvalues if they are not having a count of$max_clubs.
Full Code:
<?php
$json = <<<EOD
{
"Contestants": [
{
"Province": "Ontario",
"Classification": "LSPI,,",
"ClassificationDate": "2021",
"RegistrationStatus": "Registered",
"FirstName": "Kyle",
"LastName": "Straunf",
"Gender": "M",
"AGE": null,
"DOB": "02/08/2003",
"Clubs": [
{
"Clubname": "Penguins",
"Code": "MPNO",
"Clubid": "200"
},
{
"Clubname": "What Dolphins",
"Code": "AIZG",
"Clubid": "498"
}
],
"Email": null,
"Language": "E",
"ChallengeData": null
},
{
"Province": "Alberta",
"Classification": "LSPI,TEST2,TEST3",
"ClassificationDate": "2021",
"RegistrationStatus": "Registered",
"FirstName": "Alexander",
"LastName": "Kentwood",
"Gender": "M",
"AGE": null,
"DOB": "08/16/2005",
"Clubs": [
{
"Clubname": "Elegant Dolphins",
"Code": "ZGIA",
"Clubid": "300"
},
{
"Clubname": "Weird Dolphins",
"Code": "ZGIA2",
"Clubid": "301"
},
{
"Clubname": "Favorite Dolphins",
"Code": "ZGIA3",
"Clubid": "302"
}
],
"Email": null,
"Language": "E",
"ChallengeData": null
}
]
}
EOD;
$data = json_decode($json, true);
$max_clubs = getMaxClubs($data['Contestants']);
$headers = getHeadersRow($data['Contestants'], $max_clubs);
$fp = fopen('test.csv', 'w ');
fputcsv($fp, $headers);
fputcsv($fp, array_fill(0, count($headers), ''));// empty next line for elegance
foreach($data['Contestants'] as $contestant_data){
$row = [];
foreach($contestant_data as $key => $value){
if($key === 'Classification'){
$row = array_merge($row, explode(",", $value));
}elseif($key === 'Clubs'){
foreach($value as $club){
$row = array_merge($row, array_values($club));
}
$row = array_merge($row, array_fill(0, ($max_clubs - count($value)) * 3, ''));
}else{
$row[] = $value;
}
}
fputcsv($fp, $row);
}
fclose($fp);
function getMaxClubs($data){
$max_clubs = 0;
foreach($data as $value){
$max_clubs = max($max_clubs, count($value['Clubs']));
}
return $max_clubs;
}
function getHeadersRow($data, $max_clubs){
$columns = [];
foreach($data as $value){
foreach($value as $k => $v){
if($k === 'Classification'){
$columns['SM'] = $columns['SB'] = $columns['S'] = true;
}elseif($k === 'Clubs'){
if(count($v) !== $max_clubs) break;
$max_clubs = -1; // indicating club keys are processed to avoid repetition
$cnt = 0;
foreach($v as $club_data){
$cnt ;
foreach($club_data as $club_key => $club_value){
$columns[ $club_key. $cnt] = true;
}
}
}else{
$columns[ $k ] = true;
}
}
}
return array_keys($columns);
}