How to replace array items by splitting multiple array values by comma

Question

Given is an array of arrays:

$items = array(
  array(
    'id' => '1',
    'property_a' => 'a,b,c',
    'property_b' => '1,2,3'
    'property_c' => 'x,y'
  ),
  array(
    'id' => '2',
    'property_a' => 'b,c,d',
    'property_b' => '3,4,5',
    'property_c' => 'x,y'
  )  
);

and each item in this array should be split by some but not all values that are seperated by comma:

$splitItemsBy = array('property_a', 'property_b');

The result should be an array of items with unique combinations of the defined $splitItemsBy key values.

Desired result:

[
  ['id' => '1', 'property_a' => 'a', 'property_b' => '1', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'b', 'property_b' => '1', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'c', 'property_b' => '1', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'a', 'property_b' => '2', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'a', 'property_b' => '3', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'b', 'property_b' => '2', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'b', 'property_b' => '3', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'c', 'property_b' => '2', 'property_c' => 'x,y']
  ['id' => '1', 'property_a' => 'c', 'property_b' => '3', 'property_c' => 'x,y']

  ['id' => '2', 'property_a' => 'b', 'property_b' => '3', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'c', 'property_b' => '3', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'd', 'property_b' => '3', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'b', 'property_b' => '4', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'b', 'property_b' => '5', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'c', 'property_b' => '4', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'c', 'property_b' => '5', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'd', 'property_b' => '4', 'property_c' => 'x,y']
  ['id' => '2', 'property_a' => 'd', 'property_b' => '5', 'property_c' => 'x,y']
]

Is there an performant and elegant way to do this in PHP?

Thank you in advance for all submissions. Bonus Points for solving that in SQL. Go!

Edit: The way I solved it

To show that you don't actually do my work, here's my approach (which kind of feels too complicated).

[...]

This sounds like you just want people to write your code for you, without actually having tried yourself. — Fluffeh
– Fluffeh, Commented Sep 20, 2013 at 10:30
Edited the question and included my way to solve it to proof that you don't have to do my work. Hope answers are not going to be biased from that. — Boris
– Boris, Commented Sep 20, 2013 at 10:54
Show how the db tables are laid out if you want an SQL query. Oh, and what db you're using. — Jakub Kania
– Jakub Kania, Commented Sep 20, 2013 at 10:56
Don't have that in a DB right now and it would heavily depend on the SQL dialect. I'd say the table is all string columns but the id is int unsigned. SQLite3 would be nice but to get an idea how to approach this, any SQL dialect would be fine. — Boris
– Boris, Commented Sep 20, 2013 at 11:02
Oh and it's not that I would like to have it in SQL but if there's an easier solution to do this kind of task in SQL it would be nice. Therefore if it requires SQL features not available in "whatever-database-product" and I have to use another just to solve this problem, that is ok, hints are very welcome. — Boris
– Boris, Commented Sep 20, 2013 at 11:20

Jakub Kania · Accepted Answer · 2013-09-20 13:37:51Z

1

For PostgreSQL

SELECT id, 
  unnest(property_a) as property_a,
  property_b,
  property_c
FROM (
SELECT id, 
  property_a,
  unnest(property_b) as property_b,
  property_c
FROM
  sets) AS q;

Fiddle

Or more traditionaly with data in properties in diffrent tables (property_c could be stored in one row as a string but that's not really the point)

SELECT a.id, 
  a.property_a,
  b.property_b,
  array_agg(c.property_c) as property_c
FROM  a 
  JOIN b ON a.id=b.id
  JOIN c ON b.id=c.id
GROUP BY a.id, a.property_a,b.property_b;

Fiddle

edited Sep 20, 2013 at 13:37

answered Sep 20, 2013 at 12:37

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

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

Comments

Anthony · Accepted Answer · 2013-09-20 11:18:31Z

1

If you reorder your desired result for id = 1, it becomes a lot clearer how the groupings work:

['id' => '1', 'property_a' => 'a', 'property_b' => '1', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'b', 'property_b' => '1', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'c', 'property_b' => '1', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'a', 'property_b' => '2', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'b', 'property_b' => '2', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'c', 'property_b' => '2', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'a', 'property_b' => '3', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'b', 'property_b' => '3', 'property_c' => 'x,y']
['id' => '1', 'property_a' => 'c', 'property_b' => '3', 'property_c' => 'x,y']

Using a simplified version of the original array, I can get the above with :

$set1 = 'a,b,c';
$set2 = '1,2,3';

$array1 = explode(",",$set1);
$array2 = explode(",",$set2);

foreach($array1 as $set1_member) {
    foreach($array2 as $set2_member) {
        $collection[] = array($set1_member, $set2_member);
    }
}

Which, when I echo out the array with json_encode, returns:

[
["a","1"],
["a","2"],
["a","3"],
["b","1"],
["b","2"],
["b","3"],
["c","1"],
["c","2"],
["c","3"]
]

answered Sep 20, 2013 at 11:18

Anthony

37.2k26 gold badges103 silver badges167 bronze badges

4 Comments

Boris Over a year ago

Right, but that is very limited to just two sets. What if $splitItemsBy = array('property_a', 'property_b'); contains more properties to split? Or rather an unknown amount of properties.

Anthony Over a year ago

The number of properties in each set is not the same as the number of sets you are "combining" (for lack of a better term). The code above should handle two sets with any number of properties in either.

Boris Over a year ago

it doesn't cover if there's $set3 or an unknown amount of $setX which in the original quesion is 'property_a', 'property_b' and so on.

Anthony Over a year ago

The code can be tweaked to use foreach recursively for each set. Will need to think on best approach

Collectives™ on Stack Overflow

How to replace array items by splitting multiple array values by comma

Edit: The way I solved it

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit: The way I solved it

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related