CHEP Pallets Monitoring And Report. Step 6

Now I started working on back end to extract the data I need for my chart. I want to arrange the data into one array, that contains other associative arrays with suppliers names and then assign each suppliers name array with another associative array which contains month as a key and monthy quantity of CHEP pallets as a value. So the whole structure would look like this:

If I’m able to achieve this it will allow to use for loops at front end to draw the charts.

Coding back end with PHP

So to begin I first need to add an SQL query to PHP script to select aggregated data from database. I’ll use the same query I posted previusoly, where data is groupd by supplier and month:

$querySelectData = "
	SELECT 
		otherParty, 
		SUM(quantity) AS monthlySum, 
		yearMonthDate 
	FROM ChepReport
		WHERE transactionType = 'Transfer In' 
	GROUP BY 
		otherParty, 
		yearMonthDate 
	ORDER BY
		otherParty";
$resulSelectData = mysqli_query($shortageReportDB, $querySelectData);
$num = mysqli_num_rows($resulSelectData);

Once I get the results, I’ll use a while loop to store it in an array structure which I defined at the beggining of this post. The PHP code for it looks like this:

//an array in which I will store the data arranged in manner to draw chart at front end
$resultArray = array();
if ($num>0){
	while ($row = mysqli_fetch_array($resulSelectData, MYSQLI_ASSOC))
	{	
		$supplierName = $row['otherParty'];	
		$monthlySum = $row['monthlySum'];	
		$yearMonthDate = $row['yearMonthDate'];	
		
		//single associative array to store the data for month and quantity of pallets delivered
		$monthAndQuantity = array($yearMonthDate=>$monthlySum);
		
		if (!array_key_exists($supplierName, $resultArray)) {
			$resultArray += array($supplierName=>$monthAndQuantity);
		} else {
			array_push($resultArray[$supplierName], $monthlySum);
		}
	}
} else {
	$errors[] = 'Error! Please check that rows in excel file are not empty.';
	$arrayForFrontEnd += array("errors"=>$errors);
	Die ($jsonFile = json_encode($arrayForFrontEnd));	
}

Now let me explain the logic of this script. This code below checks if there are any rows retrieved from the database, if there are none, something didn’t work properly, but most likely an excel file was uploaded with empty rows, as I do check if all the column names required for script to work are present in the excel file at the start of PHP script, and if they are not, no SQL is perfrormed and the script gets aborted.

if ($num>0){ 
     //do something
}else {
     //report error
}

This part of the script is where I use a while loop. It loops trough all the rows that were returned from an SQL query and inside this while loop I can write a script that will fill my declared array with values.

while ($row = mysqli_fetch_array($resulSelectData, MYSQLI_ASSOC))
	{	
	     //do something
	}

Inside the while loop I wrote this code:

$supplierName = $row['otherParty'];	
$monthlySum = $row['monthlySum'];	
$yearMonthDate = $row['yearMonthDate'];	

//single associative array to store the data for month and quantity of pallets delivered
$monthAndQuantity = array($yearMonthDate=>$monthlySum);

if (!array_key_exists($supplierName, $resultArray)) {
	$resultArray += array($supplierName=>$monthAndQuantity);
} else {
	array_push($resultArray[$supplierName], $monthlySum);
}

From each retrieved row, I assign supplier name, monthy sum of pallets delivered and the month of the year to PHP variables. I also declare an associative array with month value as it’s key and quantity of pallets delivered as it’s value.

$supplierName = $row['otherParty'];	
$monthlySum = $row['monthlySum'];	
$yearMonthDate = $row['yearMonthDate'];

//single associative array to store the data for month and quantity of pallets delivered
$monthAndQuantity = array($yearMonthDate=>$monthlySum);

Since I want to store these values in an associative array, and achieve this structure:

the first thing I do is check if there is already an associative array with suppliers name. The logic of this script is explained below.

if (!array_key_exists($supplierName, $resultArray)) {
	//if an associative array with suppliers name does not exist in a result array
	//add an associative array to result array with suppliers name as key and monthAndQuantity array as it's value
	//like this ---> $resultArray += array($supplierName=>$monthAndQuantity)
} else {
	//if an associative array with supplier name exists in the result array
	//it means that I just need to add month and quantity values to an already existing array
	//I do it with this code --> array_push($resultArray[$supplierName], $monthlySum);
}

Now to be honest my initial logic for this part of the code was different:

if (!array_key_exists($supplierName, $resultArray)) {
        $resultArray += array($supplierName=>$monthAndQuantity);
} else {
        array_push($resultArray[$supplierName], $monthlySum);
}

As to me it appeard that instead of adding $monthlySum variable to the array, I need to add $monthAndQuantity array instead. However if I excecute this code:

 array_push($resultArray[$supplierName], $monthAndQuantity);

it produces this type of result:

Which shows that the first key-value pair inside suppliers name array is correct, however every additional one created using array_push method is not: instead of having a value of pallet quantity it has a value of a $monthAndQuantity array. So once I spotted it I replaced the $monthAndQuantity variable inside the array_push method with $monthlySum variable and achieved the desired result.

Though it still leaves me baffled, as I didn’t anticipate that the month value will be already in this array, but since I found a solution to fix it I don’t want to dwell on it too much 😉

Even though all the data retrieved from the database is now stored in a $resultArray, it is still not done as I haven’t achieved my desired structure: the data in database is for half a year, so if a supplier hasn’t delivered any pallets for at least one month, there are no 0 values for that month in suppliers associative array. In the last image, a supplier has 3 month=>quantity pairs, though it should be 6, which means that 3 value pairs where a pallet quantity is 0 are still missing in this array. I will work to get around this later 😉

Added updated files for this project to my github https://github.com/kachiuz/chep-report