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

Question

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 · Accepted Answer · 2016-06-18 07:15:36Z

2

Assuming any given LineItemNo would always be paired with the same SolNo and St, you can pass LineItemNo of each row to a function to check if the value exists in the sub-arrays of the 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;
while($r = mysql_fetch_assoc($results)) {
    $array[]=$r;
}

foreach ($array as $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;
    }
}
//function for subarray search
function searchSub($arr, $keyword){
    foreach($arr as $key=>$value)
    {
        if (in_array($keyword, $value, true)!=false){
        return $key;
        }
    }
    return false;
}
$output['data']=$rs;
print_r($output);

Test Result

edited Jun 18, 2016 at 7:15

answered Jun 18, 2016 at 3:49

SML

1,2756 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Mike Barwick Over a year ago

Why the counter? $key will do the exact same thing. It's redundant. Also why the search function?

SML Over a year ago

@MikeBarwick the OP wants to move parts_info that shares the same lineitemno, solno and st to as sub arrays to the array parts, not just move part_info to a sub array. I hope I didn't misinterpret the questoin

SML Over a year ago

@MikeBarwick $key would result in 5 sub-arrays, at the level after 'data', the output the OP wants should only have 2 sub-arrays at the level after 'data'

Mike Barwick Over a year ago

No. There's a good chance I have lol I give up. Hopefully this solves OPs. :)

BobRodes Over a year ago

No, SML, you interpreted the question correctly. Thanks for taking a crack at this. I find when I run this code, all the values are '1' for the first element (including all the values for parts), 'S', for the second one, 'O' for the third, and 'F' for the fourth. Not sure where that's coming from, but I had something similar when messing with foreach. Also, there are four parts for the first, and two for the next three. There should be two top-level elements (removing data until the end is a good idea), with three parts each.

|

Mike Barwick · Accepted Answer · 2016-06-18 02:12:41Z

2

array_push is the way I'd typically do it. You're code is a bit confusing to read (not very user friendly), but I think you're close - very close...

EDIT: Check out the below. Should work. Change your while to a foreach and run it like so:

$rs = [];

foreach(mysql_fetch_array($result) as $key => $row) 
{
    $rs['data'][$key] = array('LineItemNo' => $row['LineItemNo'], 'SolNo' => $row['SolNo'], 'St' => $row['St']);

    $part_info = array('part_no' => $row['PartNo'], 'unit_price' => $row['UnitPrice']);
    $rs['data'][$key]['parts'] = $part_info;
}

print_r($rs);

edited Jun 18, 2016 at 2:12

answered Jun 18, 2016 at 0:23

Mike Barwick

5,3777 gold badges56 silver badges78 bronze badges

12 Comments

BobRodes Over a year ago

Thanks Mike. That's closer, but not all the way. I only get the parts for the first top-level element. I'll edit my post to incorporate this.

BobRodes Over a year ago

Ok, not sure how to do that I guess. I've edited my answer as well to show what I've tried. Oh, I had to use array() instead of [], for some reason I was getting a syntax error. Might that have anything to do with the problem?

Mike Barwick Over a year ago

Check my edits for some ideas. Note, $rs[data][parts] should be $rs['data']['parts']

BobRodes Over a year ago

Curiously, I thought the keys had to be in quotes too, but I tried them without and they worked.

Mike Barwick Over a year ago

Prob explains why [] weren't working as na array - as it's the same thing. What PHP version you on?

|

Collectives™ on Stack Overflow

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

2 Answers 2

15 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related