BobRodes BobRodes - 5 months ago 15
PHP Question

PHP: how to merge one associative array into a sub-array of another multidimensional array

I have a flat resultset, that looks like this in JSON:

[{
"LineItemNo": "1",
"SolNo": "SPE8E7-15-T-1379",
"St": "O",
"PartNo": "F13DBX\/SPX35\/4P",
"UnitPrice": "1.890"
}, {
"LineItemNo": "1",
"SolNo": "SPE8E7-15-T-1379",
"St": "O",
"PartNo": "CF13DD\/E\/835",
"UnitPrice": "1.920"
}, {
"LineItemNo": "1",
"SolNo": "SPE8E7-15-T-1379",
"St": "O",
"PartNo": "QT13\/35-4",
"UnitPrice": "1.770"
}, {
"LineItemNo": "2",
"SolNo": "SPE8E7-15-T-4309",
"St": "O",
"PartNo": "F13DBX\/SPX35\/4P",
"UnitPrice": "1.890"
}, {
"LineItemNo": "2",
"SolNo": "SPE8E7-15-T-4309",
"St": "O",
"PartNo": "CF13DD\/E\/835",
"UnitPrice": "1.920"
}, {
"LineItemNo": "2",
"SolNo": "SPE8E7-15-T-4309",
"St": "O",
"PartNo": "QT13\/35-4",
"UnitPrice": "1.770"
}]


I want to change it into this:

{
"data": [{
"LineItemNo": "1",
"SolNo": "SPE8E7-15-T-1379",
"St": "O",
"Parts": [{
"PartNo": "F13DBX\/SPX35\/4P",
"UnitPrice": "1.890"
}, {
"PartNo": "CF13DD\/E\/835",
"UnitPrice": "1.920"
}, {
"PartNo": "QT13\/35-4",
"UnitPrice": "1.770"
}]
}, {
"LineItemNo": "2",
"SolNo": "SPE8E7-15-T-4309",
"St": "O",
"Parts": [{
"PartNo": "F13DBX\/SPX35\/4P",
"UnitPrice": "1.890"
}, {
"PartNo": "CF13DD\/E\/835",
"UnitPrice": "1.920"
}, {
"PartNo": "QT13\/35-4",
"UnitPrice": "1.770"
}]
}]
}


I've tried numerous things, but I haven't been able to find a way to iterate through the source array, create a
Parts
array during the process, and then plug values into that array during subsequent iterations. What I'd like to do is iterate the source array, check the
$row['LineItemNo']
value, and if it has changed since the last iteration, create a new top-level array object. If it hasn't, then create a new element the
Parts
array.

My basic code looks like this (I realize mysql is deprecated in favor of mysqli or PDO, but I'm stuck with it for the time being):

$result = mysql_query($query) or die(mysql_error());
$rs = array();
$rsParts = array();
while($row = mysql_fetch_array($result))
{
if (!isset($ln) || $row['LineItemNo'] != $ln)
{
$ln = $row['LineItemNo'];
$rs[data][] = array('LineItemNo' => $row['LineItemNo'], 'SolNo' => $row['SolNo'], 'St' => $row['St']);
$rs[data][parts] = array();
}
array_push($rs[data][parts], array('PartNo' => $row['PartNo'], 'UnitPrice' => $row['UnitPrice']));
}


This is a good start, but it only gives me the parts in the first element, like this:

{
"data": {
"0": {
"LineItemNo": "1",
"SolNo": "SPE8E7-15-T-1379",
"St": "O"
},
"parts": [{
"PartNo": "F13DBX\/SPX35\/4P",
"UnitPrice": "1.890"
}, {
"PartNo": "CF13DD\/E\/835",
"UnitPrice": "1.920"
}, {
"PartNo": "QT13\/35-4",
"UnitPrice": "1.770"
}],
"1": {
"LineItemNo": "2",
"SolNo": "SPE8E7-15-T-4309",
"St": "O"
}
}
}


I'm thinking that you can't re-initialize the array this way. I've tried unsetting it when the LineItemNo changes, but that turns all the
Parts
elements into top-level elements. I've tried only initializing the array at the beginning using
is_array
, but that puts all of the parts into the first top-level element (there are six parts in the first one and none in the second).

So, what's the right way to do what I'm trying to do?

EDIT: the solution of the problem was to first send the resultset (
$result
) through this code:

$array = array();
while($r = mysql_fetch_assoc($result))
{
$array[] = $r;
}


And then use SML's solution from there.

SML SML
Answer

Assuming any particular LineItemNo would always have the same SolNo and St, you can pass LineItemNo of each row to a function that checks if the value exists in the sub-arrays of result array '$rs'.

If LineItemNo doesn't already exist in $rs, then a new first level sub array of $rs is created and a 2nd level sub array containing parts info will also be added as sub_array of 1st level array element parts.

If LineItemNo does exist, then just add the parts info as sub_array of 1st level array element parts.

Since the top level array ['data'] you wanted in the output, doesn't do anything at this stage.
I strip that from the process and instead $rs is added to $output['data'] in the final stage to form the output you need.

$rs=array();
$counter=0;
foreach(mysql_fetch_array($result) as $key => $row) { 
    $part_info = array('part_no' => $row['PartNo'], 'unit_price' => $row['UnitPrice']);
    $temp=searchSub ($rs, $row['LineItemNo']);
    if ($temp===false){
            $rs[$counter] = array('LineItemNo' => $row['LineItemNo'], 'SolNo' => $row['SolNo'], 'St' => $row['St']);
            $rs[$counter]['parts'][] = $part_info;
            $counter++;
    }
    else {
            $rs[$temp]['parts'][]= $part_info;
    }
}
$output['data']=$rs;
print_r($output);

//function to search sub-array 
function searchSub($arr, $keyword){
    foreach($arr as $key=>$value)
    {
        if (in_array($keyword, $value, true)!=false){
        return $key;
        }
    }
    return false;
}

Test Result