Organizing array Iteration generated from nested loops PHP

I have a loop that fetches data from an excel file using phpspreadsheet. data fetching has no problem, but what I want to do is organize the data to a multi-dimensional array then convert it to JSON so I can access it through javascript.

here’s the code:

    <?php require 'vendor/autoload.php';

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

// Variables
$spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load("test.xlsx"); // read existing spreadsheet

$worksheet = $spreadsheet->getSheetByName("table1"); // get specific sheet 
$lastRow = $spreadsheet->getActiveSheet()->getHighestRow(); // get highest loaded row

$startRow = 3; // start cell to loop
$typeStart = 2; // start of type column

$column = [
    "B",
    "C",
    "D",
    "E",
    "F"
];

$data = array();

for ($row = $startRow; $row <= $lastRow; $row++) {

    $phoneType = $worksheet->getCell("A" . $typeStart)->getValue(); // get phone type
    echo $phoneType . "<br>"; // display current phone type

    $typeStart = $typeStart + 6; // iterate to next phone type
    $currentRow = $row; // add 2 to start value of issues

    foreach ($column as $col) {  // loop through the 5 columns
        $compName = $worksheet->getCell($col . 1)->getValue();
        echo "Company Name: " . $compName . "<br>";

        for ($i = $currentRow; $i < $typeStart; $i++) {
            $issueName = $worksheet->getCell("A" . $i)->getValue();
            $issueCost = $worksheet->getCell($col . $i)->getValue();

            $cost = array($issueName => $issueCost);
            var_dump($cost);
            echo "<br>";
        }
    }

    $row = $row + 5;
}

when I run this code, it shows this text:

IphoneX
Company Name: company1
array(1) { ["cracked"]=> int(7300) }
array(1) { ["LCD"]=> int(7400) }
array(1) { ["Water"]=> int(7500) }
array(1) { ["Data"]=> int(7600) }
array(1) { ["Network"]=> int(7700) }
Company Name: company2
array(1) { ["cracked"]=> int(8300) }
array(1) { ["LCD"]=> int(8400) }
array(1) { ["Water"]=> int(8500) }
array(1) { ["Data"]=> int(8600) }
array(1) { ["Network"]=> int(8700) }
Company Name: company3
array(1) { ["cracked"]=> int(7300) }
array(1) { ["LCD"]=> int(7400) }
array(1) { ["Water"]=> int(7500) }
array(1) { ["Data"]=> int(7600) }
array(1) { ["Network"]=> int(7700) }
Company Name: company4
array(1) { ["cracked"]=> int(8300) }
array(1) { ["LCD"]=> int(8400) }
array(1) { ["Water"]=> int(8500) }
array(1) { ["Data"]=> int(8600) }
array(1) { ["Network"]=> int(8700) }
Company Name: company5
array(1) { ["cracked"]=> int(7300) }
array(1) { ["LCD"]=> int(7400) }
array(1) { ["Water"]=> int(7500) }
array(1) { ["Data"]=> int(7600) }
array(1) { ["Network"]=> int(7700) }
IphoneXR
Company Name: company1
array(1) { ["cracked"]=> int(7300) }
array(1) { ["LCD"]=> int(7400) }
array(1) { ["Water"]=> int(7500) }
array(1) { ["Data"]=> int(7600) }
array(1) { ["Network"]=> int(7700) }
Company Name: company2
array(1) { ["cracked"]=> int(8300) }
array(1) { ["LCD"]=> int(8400) }
array(1) { ["Water"]=> int(8500) }
array(1) { ["Data"]=> int(8600) }
array(1) { ["Network"]=> int(8700) }
Company Name: company3
array(1) { ["cracked"]=> int(7300) }
array(1) { ["LCD"]=> int(7400) }
array(1) { ["Water"]=> int(7500) }
array(1) { ["Data"]=> int(7600) }
array(1) { ["Network"]=> int(7700) }
Company Name: company4
array(1) { ["cracked"]=> int(8300) }
array(1) { ["LCD"]=> int(8400) }
array(1) { ["Water"]=> int(8500) }
array(1) { ["Data"]=> int(8600) }
array(1) { ["Network"]=> int(8700) }
Company Name: company5
array(1) { ["cracked"]=> int(7300) }
array(1) { ["LCD"]=> int(7400) }
array(1) { ["Water"]=> int(7500) }
array(1) { ["Data"]=> int(7600) }
array(1) { ["Network"]=> int(7700) }

Now I want to organize this to an array that looks like this:

{
 "phoneType" : "IphoneX"
   {
     {
      "companyName": "Company1",
      "cracked" : 7300,
      "LCD" : 7400,
      "Water" : 7500,
      "Data" : 7600,
      "Network" : 7700
     }
     {
      "companyName": "Company2",
      "cracked" : 8300,
      "LCD" : 8400,
      "Water" : 8500,
      "Data" : 8600,
      "Network" : 8700
     }
   }
"phoneType" : "IphoneXR"
   {
     {
      "companyName": "Company1",
      "cracked" : 7300,
      "LCD" : 7400,
      "Water" : 7500,
      "Data" : 7600,
      "Network" : 7700
     }
     {
      "companyName": "Company2",
      "cracked" : 8300,
      "LCD" : 8400,
      "Water" : 8500,
      "Data" : 8600,
      "Network" : 8700
     }

   }
}

Is it achievable? thanks in advance.

Answer

You need to push data with specified, first-level keys in your foreach() loop. Don’t bother giving a key to the grouping value (phone type), just use that value as the grouping key.

foreach ($column as $col) { 
    $compName = $worksheet->getCell($col . 1)->getValue();
    $set = ['companyName' => $compName]; // reset this subarray and populate with company name
    for ($i = $currentRow; $i < $typeStart; $i++) {
        $issueName = $worksheet->getCell("A" . $i)->getValue();
        $issueCost = $worksheet->getCell($col . $i)->getValue();
        $set[$issueName] = $issueCost;
    }
    $result[$phoneType][] = $set;
}

Advertisement

Answer