CHEP Pallets Monitoring And Report. Step 10

For a third chart I will use a different approach to store data into a multidimensional array. If for suppliers I first filled array with existing values from database, and added 0 values later, then know I will first create a multidimensional array’s frame. I can do that because I have transfer type names and month values. The structure of this multidimensional array is displayed in the picture below.

Since I’m first creating arrays where I know the key names for nested arrays (transfer type name and month value) the only value that’s left to add is quantity of pallets for each month, which will be set to 0. Then these arrays will be added to another array which is named $dataForTransferTypeChart. The code for creating this multidimensional array is below:

//loops to create data structure to store monthly sum of pallets grouped by transfer type
$dataForTransferTypeChart = array();
for ($i = 0; $i <Count($transferTypesNamesArray); $i++){
	for ($x = 0; $x <$numberOfMonths; $x++){			
		$dataForTransferTypeChart[$transferTypesNamesArray[$i]][$distinctMonths[$x]]= 0;
	}
}

After executing this script and checking response in Chrome DevTools, I can see that it worked as intended. The only issue is that month values are not in order, but I will sort this out in next step.

So to have month values in array by order from latest to most recent one I have to update the query where distinct month names are selected and add an ORDER BY clause that ascends results by yearMonthDate values. As these values are used to create keys for $transferTypesNamesArray, once these values in $distinctMonths array are ordered, the keys for $transferTypesNamesArray will be created in the same order.

//a query to select distinct month values 
$querySelectDistinctMonths = "
	SELECT 
		DISTINCT(yearMonthDate) AS distinctMonths
	FROM ChepReport
		WHERE transactionType = 'Transfer In'
	ORDER BY 
		yearMonthDate ASC";

After adding the ORDER BY clause and running the script again, I can see that now the empty array has it’s keys in order from smallest to largest.

Now it’s time to add values to this multidimensional array. Once the data is retrieved from database values that are not 0 from this query will be updated in the array. The PHP script for retrieving data from database using an SQL query and updating the array is demonstrated below.

//select data from database for chart 3
$querySelectDataForChart3 = "
	SELECT 
		transactionType, 
		SUM(quantity) AS monthlySum, 
		yearMonthDate 
	FROM ChepReport
		WHERE 1=1 
	GROUP BY 
		transactionType, 
		yearMonthDate 
	ORDER BY
		yearMonthDate";
$resulSelectDataForChart3 = mysqli_query($shortageReportDB, $querySelectDataForChart3);
$num = mysqli_num_rows($resulSelectDataForChart3);

if ($num>0){
	while ($row = mysqli_fetch_array($resulSelectDataForChart3, MYSQLI_ASSOC))
	{	
		$transactionType = html_entity_decode($row['transactionType']);	
		//for a chart I don't want negative values, hence the abs() function is used here
		$monthlySum = abs(ROUND($row['monthlySum'],0));	
		$yearMonthDate = $row['yearMonthDate'];
		//update empty associative array value
		$dataForTransferTypeChart[$transactionType][$yearMonthDate]= $monthlySum;
	}
} else {
	ReportErrorForEmptyExcelFile();
}

So what this script does is simple: once it retrieves data from database it uses $transactionType and $yearMonthDate values from each row as keys to access deepest subarray and update value from 0 to $monthlySum. After running this script I can see this result in Chromes DevTools:

This approach seems a lot smoother then creating arrays with data retrieved from database and then checking if key exists to add 0 values. It’s cleaner code and I don’t need to use array_key_exists function at all.

So data for my third chart has been collected and available at front end, however I want to order my first chart data by ascending month value order, just like I did with this one.

Arranging data in ascending order for first chart

Since I arranged data in ascending order for my third chart, I now want to do the same thing for the data I have collected for my first chart, as ordered data will make it easier to draw charts at front end. Since these arrays were constructed in a different manner: it first adds key-value pairs that have values from database, and then adds key-value pairs with zero values, so there is no order.

To achieve order in each supplier subarray I will use PHP ksort() function.

for ($i = 0; $i <$numberOfSuppliers; $i++){
	for ($x = 0; $x <$numberOfMonths; $x++)
		if(!array_key_exists($distinctMonths[$x], $resultArray[$distinctSuppliersNamesArray[$i]]))
			$resultArray[$distinctSuppliersNamesArray[$i]][$distinctMonths[$x]]= 0;
		}		
	}
	//sort the the inner array within suppliers name distincsMonths in ascending order.
	ksort($resultArray[$distinctSuppliersNamesArray[$i]]);
}

And after checking Chrome DevTools I can see that ksort() did it’s job!

I could just rewrite the code and apply the same method of creating multidimensional array that I used to store data for my third chart, however I want to leave this code as it is, as even though this is a more complicated way to do things, it still did it’s job and I might have to use this some other time.

On my next post I will write about the second chart again, as I will do it differently again… 🙂 just realized a better way to do it 🙂