I’ve been working on a project that pulls data from a DB2 database and formats it for the end user.
I have a class for each type of output (currently only one type exists), that constructs an associative array to specify what tables to pull from and what columns to grab from each table ($capturegroups). My controller gathers that information and uses it to construct SQL queries that are sent to the database and return values in an associative array ($output).
Here is an example of the $captureGroups array (in the format of “alias” => “columname”):
"lineitem" => array(
"schema1.table1" => array(
"item" => "POISQ",
"qty" => "QTYOR",
"unit" => "UMORD",
"bnc part no." => "ITNBR",
"description" => "ITDSC",
"dock date" => "DUEDT", # or DOKDT?
"acct" => "ACCTN",
"unit price" => "STKPR",
"total" => "EXTPR",
),
"schema1.table2" => array(
"item" => "POISQ",
"comment 1" => "CMNT1",
"comment 2" => "CMNT2"
)
I use this to generate an sql query in the format:
SELECT columnName, columnName, columnName FROM scheme.table WHERE orderKey='userInput' ORDER BY orderKey
Here is a sample of the $output array (print_r() formatting):
Array
(
[geninfo] => Array
(
[noquery] => Array
(
[for sale] =>
[for use] =>
)
[schema1.table0] => Array
(
[0] => Array
(
[order date] => 1180316
[shipped via] =>
[terms] =>
)
)
)
Yesterday, I hit a brick wall. My view file (the template that formats the data) is based mostly on foreach loops that know very little about the data that is coming to them.
The PHP that I am having issues with generates an HTML table, with a simple foreach loop that adds a <th> field to the <thead> for every “key” in the specified array, then adds a <td> to the <tbody> for each “value”. However, I need the HTML table to contain data for two separate arrays, and I am not sure how to make that happen.
The first array contains line item data:
[lineitem] => Array
(
[schema1.table1] => Array
(
[0] => Array
(
[item] => 1
[qty] => 5.000
[unit] => EXAMPLE
[part no.] => 1234567
[description] => EXAMPLE DESC
[dock date] => 1180423
[acct] => 130
[unit price] => 123.4500
[total] => 617.2500
)
The array that I need to join this with contains comments pertaining to each line item. The comments have an identical <“item” => int> pair to the line item they belong to.
([lineitem]...)
[schema1.table2] => Array
(
[0] => Array
(
[item] => 1
[comment 1] => THIS ITEM DCK DATE WAS CHANGED
[comment 2] => WAS 04/02/18, CHANGED 04/03/18
)
My question: is there a way to marry/join the comment data with the line item data based on the common key=>value pair in the $output array? I want the data to be accessible essentially like so:
[lineitem]
[0] => array
(
[item] => 1
[qty] => 5.000
[unit] => EXAMPLE
[part no.] => 1234567
[description] => EXAMPLE DESC
[dock date] => 1180423
[acct] => 130
[unit price] => 123.4500
[total] => 617.2500
[comment 1] => THIS ITEM DCK DATE WAS CHANGED
[comment 2] => WAS 04/02/18, CHANGED 04/03/18
)
Doing this by joining the current output arrays would be far more convenient than the most obvious alternative, which is to start using three-part namespaces and SQL joins whenever a capturegroup (eg “lineitems”) draws from multiple tables. Using 3-part namespaces and joins would require me to rewrite / restructure the $captureGroups array, the code that generates my SQL queries, and probably reformat the $output to work with the “view”/template file - something that I’m not entirely against doing, but would like to avoid if possible.
I’ve looked at array_merge and similar functions but I can’t seem to figure out how to get things together based on the identical key=>value pairs.