Group 2d array rows by identifying column value and append incremented number to the keys of subsequent group data

Question

I have a huge array coming back as search results and I want to do the following:

Walk through the array and for each record with the same "spubid" add the following keys/vals: "sfirst, smi, slast" to the parent array member in this case, $a[0]. So the result would be leave $a[0] in tact but add to it, the values from sfirst, smi and slast from the other members in the array (since they all have the same "spubid"). I think adding the key value (1, 2, 3) to the associate key (sfirst1=> "J.", smi1=>"F.", slast1=>"Kennedy") would be fine. I would then like to DROP (unset()) the rest of the members in the array with that "spubid". Here is a simplified example of the array I am getting back and in this example all records have the same "spubid":

Array ( 
  [0] => 
    Array ( 
      [spubid] => A00502 
      [sfirst] => J. 
      [smi] => A. 
      [slast] => Doe
  [1] => 
    Array ( 
      [spubid] => A00502 
      [sfirst] => J. 
      [smi] => F. 
      [slast] => Kennedy 
  [2] => 
    Array ( 
      [spubid] => A00502 
      [sfirst] => B. 
      [smi] => F. 
      [slast] => James 
  [3] => 
    Array ( 
      [spubid] => A00502
      [sfirst] => S. 
      [smi] => M. 
      [slast] => Williamson 
      )
    )

So in essence I want to KEEP $a[0] but add new keys=>values to it (sfirst$key, smi$key, slast$key) and append the values from "sfirst, smi, slast" from ALL the members with that same "spubid" then unset $a[1]-[3].

Just to clarify my IDEAL end result would be:

Array ( 
  [0] => 
    Array ( 
      [spubid] => A00502 
      [sfirst] => J. 
      [smi] => A. 
      [slast] => Doe
      [sfirst1] => J.
      [smi1] => F. 
      [slast1] => Kennedy
      [sfirst2] => B. 
      [smi2] => F. 
      [slast2] => James 
      [sfirst3] => S. 
      [smi3] => M. 
      [slast3] => Williamson
    )
  )

In most cases I will have a much bigger array to start with that includes a multitude of "spubid"'s but 99% of publications have more than one author so this routine would be very useful in cleaning up the results and making the parsing process for display much easier.

***UPDATE

I think by over simplifying my example I may have made things unclear. I like both Chacha102's and zombat's responses but my "parent array" contains A LOT more than just a pubid, that just happens to be the primary key. I need to retain a lot of other data from that record, a small portion of that is the following:

[spubid] => A00680 
[bactive] => t 
[bbatch_import] => t 
[bincomplete] => t 
[scitation_vis] => I,X 
[dentered] => 2009-08-03 12:34:14.82103 
[sentered_by] => pubs_batchadd.php 
[drev] => 2009-08-03 12:34:14.82103 
[srev_by] => pubs_batchadd.php 
[bpeer_reviewed] => t 
[sarticle] => A case study of bora-driven flow and density changes on the Adriatic shelf (January 1987)
.
.
.
.
.

There are roughly 40 columns that come back with each search query. Rather than hard coding them as these examples do with the pubid, how can I include them while still making the changes as you both suggested. Creating a multi-dimensional array (as both of you suggested) with the authors being part of the multi-dimension is perfectly fine, thank you both for the suggestion.

**** UPDATE:

Here is what I settled on for a solution, very simple and gets the job done nicely. I do end up creating a multi-dimensional array so the authors are broken out there.

Over simplified solution:

$apubs_final = array();
$spubid = NULL;
$ipub = 0;

foreach($apubs as $arec)
{
  if($spubid != $arec['spubid'])
  {
    $ipub++;
    $apubs_final[$ipub] = $arec;
    // insert UNSET statements here for author data
    $iauthor = 0;
    $spubid = $arec['spubid'];
  }
  $iauthor++;
  $apubs_final[$ipub]['authors'][$iauthor]['sauthor_first'] = $arec['sfirst'];
}

Might it be easier to get an array of author arrays, each one containing sfirst, smi and slast? — Tyler Carter
– Tyler Carter, Commented Aug 3, 2009 at 22:32
There, I did it both ways for you on my answer incase you are open to switching to a different format. — Tyler Carter
– Tyler Carter, Commented Aug 3, 2009 at 22:49
What do you do with the array you end up with? Why does it need to be in that format? — rojoca
– rojoca, Commented Aug 4, 2009 at 1:37

Tyler Carter · Accepted Answer · 2009-08-03 23:42:41Z

// First, probably the more parsable way.
foreach($array as $key => $values)
{
    $end[$spuid] = $values;
    $spuid = $values['spuid']
    $end[$spuid]['authors'][] = array('sfirst' => $values['sfirst'],
                          'smi' => $values['smi'],
                           'slast' => $values['slast']);

}

Which will get an array like this

Array(
    [A00502] =>
         Array(
           [supid] => A00502
               .... other values .....
           [authors] =>
                 Array(
                [0]=>
                      Array(
                    ['sfirst'] => '',
                    ['smi'] => '',
                    ['slast'] => '')
                )
        )
)

I find this way to be much more parse-able if you plan on showing it on a page, because it uses arrays so you can foreach the authors, which is how I've seen many people do it for attributes like that.

If you really do want your ideal format, use this afterwards

$count = 0;
foreach ($end as $supid => $values)
{
    $other_end[$count] = $values;
    $other_end[$count]['spuid'] = $spuid;
    foreach($values['authors'] as $key => $author)
    {
        if($key == 0)
        {
            $suffix = '';
        }
        else
        {
            $suffix = $key;
        }
        $other_end[$count]['sfirst'.$suffix] = $author['sfirst'];
        $other_end[$count]['smi'.$suffix] = $author['smi'];
        $other_end[$count]['slast'.$suffix] = $author['slast'];
    }

}

Great solution, I edited the original post because I slightly over simplified things thus making my overall question unclear. I apologize!
Updated, it is very similar, and you might have a few extra keys, but it will all be there.
Basically, you will have the extra first middle and last name of the last person in the array, but that shouldn't affect it much.
You updated solution got me to where I needed to get, I edited my original post with the solution I settled on. Thank you again VERY MUCH for everything.
You know everyone who answered this is waiting to see who you give the accepted answer. :P

gnarf · Accepted Answer · 2009-08-04 19:44:15Z

Why not instead make an array keyed on the spubid:

// assuming $array is your array:

$storage = array();
foreach($array as $entry) {
  $bid = $entry['spubid'];
  if (!isset($storage[$bid])) {
    // duplicate entry - taking the author out of it.
    $stortmp = $entry;
    unset($stortmp['sfirst'], $stortmp['smi'], $stortmp['slast']);
    // add an authors array
    $stortmp['authors'] = array();
    $storage[$bid] = $stortmp;
  }
  $author = array(
    'sfirst' => $entry['sfirst'], 
    'smi' => $entry['smi'], 
    'slast' => $entry['slast']);
  $storage[$bid]['authors'][] = $author;
}

Now your $storage array should look like:

Array(
  "A00502" => Array(
    "spubid" => "A00502",
    "authors" => Array(
      [0] => 
        Array ( 
          [sfirst] => J. 
          [smi] => A. 
          [slast] => Doe
       [1] => 
         Array ( 
          [sfirst] => J. 
          [smi] => F. 
          [slast] => Kennedy

And you could easily do a foreach on the authors to print them:

foreach ($storage as $pub) {
  echo 'Pub ID: '.$pub['spubid']."<br/>";
  foreach ($pub['authors'] as $author) {
    echo 'Author: '.$author['sfirst'].' '.$author['smi'].' '.$author['slast']."<br/>";
  }
}

And as an added bonus, you can access $storage['A00502'].

UPDATED FOR COMMENT

It seems that your array is probably coming from some sort of SQL query that involves a JOIN from a publications table to a authors table. This is making your result dataset duplicate a lot of information it doesn't really need to. There is no reason to have all the publication data transferred/retrieved from the database multiple times. Try rewriting it to get a query of all the books its going to display, then have a "authors" query that does something like:

SELECT * FROM authors WHERE spubid IN ('A00502', 'A00503', 'A00504');

Then convert it into this array to use for your display purposes. Your database traffic levels will thank you.

This doesn't work as intended, I am still carrying over all the duplicated data. I oversimplified my example in the original post which I am sure was misleading. I have since expanded on the example.
I've edited the example to show you how to keep all of the data, but makes me think your problem is more about your dataset. There is a section at the end of the answer about that.
This was actually my initial approach, using one query to get all the pub info then a separate one to grab the authors. I personally like this approach better as you are only querying the database for exactly the data that you need. I was however told by the powers that be I can not use this method and need to do it all with a single query. Hence the NESTED joins...

zombat · Accepted Answer · 2009-08-03 22:55:44Z

This code should work exactly as you specified. I took the approach of using a couple of temporary arrays to do correlations between the main array keys and the spubid sub-keys.

/* assume $array is the main array */
$array = array(
    array('spubid' => 'A00502','sfirst'=>'J.','smi'=>'A.','slast'=>'Doe'),
    array('spubid' => 'A00502','sfirst'=>'J.','smi'=>'F.','slast'=>'Kennedy'),
    array('spubid' => 'A00502','sfirst'=>'B.','smi'=>'F.','slast'=>'James'),
    array('spubid' => 'BXXXXX','sfirst'=>'B.','smi'=>'F.','slast'=>'James'),
    array('spubid' => 'A00502','sfirst'=>'S.','smi'=>'M.','slast'=>'Williamson')
);

//track spubid positions in the main array
$keyPositions = array();
//keys to delete after array iteration
$keyDel = array();
//track how many spubkey increments you've made to the fields
$spubKeys = array();
//fields to copy between spubids
$copyFields = array('sfirst','smi','slast');

foreach($array as $key => $subarr)
{
    if (isset($subarr['spubid'])) {
        if (isset($keyPositions[$subarr['spubid']])) {
            //spubid already exists at a main array key, do the copy
            $spubKey = ++$spubKeys[$subarr['spubid']];
            foreach($copyFields as $f) {
                $array[$keyPositions[$subarr['spubid']]][$f.$spubKey] = $subarr[$f];
            }
            $keyDel[] = $key;
        }
        else {
            //First time encountering this spubid, mark the position
            $keyPositions[$subarr['spubid']] = $key;
            $spubKeys[$subarr['spubid']] = 0;
        }
    }
}
if (count($keyDel)) {
    foreach($keyDel as $idx) unset($array[$idx]);
}

var_dump($array);

Edited the original post due to some over simplification of the data in question. Could you take another look? Thanks again for the great solution.

mickmackusa · Accepted Answer · 2023-11-26 22:20:39Z

Assign references to avoid keeping track of where group data is being pushed into the result array. Also maintain a counter variable to determine if and what integer should be appended to the keys of subsequently encountered datasets.

Code: (Demo)

$result = [];
foreach ($array as $row) {
    $id = $row['spubid'];
    if (!isset($ref[$id])) {
        $ref[$id] = $row;
        $counter[$id] = 0;
        $result[] = &$ref[$id];  // push group reference into result array
    } else {
        unset($row['spubid']);
        ++$counter[$id];
        foreach ($row as $k => $v) {
            $ref[$id][$k . $counter[$id]] = $v; // push assoc elements into group reference
        }
    }
}
var_export($result);

Personally, I find this to be an XY Problem -- you probably shouldn't be appending numbers to express subgroups. In a professional application, I'd be using deeper subarrays (per group).

Collectives™ on Stack Overflow

Group 2d array rows by identifying column value and append incremented number to the keys of subsequent group data

4 Answers 4

6 Comments

3 Comments

1 Comment

Comments

Hot Network Questions