Group rows of a 2d array by a column and create subarray elements from another column

Question

I want to get a list of all tasks grouped by task and all of the users assigned to it.

Something like this:

Task id: 1, name: do dishes, users: bob, liam.

I only got to print a task twice if there were 2 users assigned to it.

The CodeIgniter query returns a 2d array via result() with 2 rows:

1 for the task with the user bob and
1 for the same task with the user liam.


array{
      array{
            task_id = "1",
            name = "do dishes",
            user = "bob"
           }

      array{
            task_id = "1",
            name = "do dishes",
            user = "liam"
           }

      array{
            task_id = "2",
            name = "vacuum",
            user = "liam"
           }

      array{
            task_id = "3",
            name = "Take out thrash",
            user = "liam"
           }

      array{
            task_id = "3",
            name = "Take out thrash",
            user = "bob"
           }
}

What I want to get is a result with 1 row containing the task and within that row I want an array with each name of the users assigned to it.

array{
      array{
            task_id = "1",
            name = "do dishes",
            user = array( "bob", "liam" )
           }
      array{
            task_id = "2",
            name = "vacuum",
            user = array( "liam" )
           }
      array{
            task_id = "3",
            name = "Take out thrash",
            user = array( "liam", "bob" )
           }
}

Is there any way to achieve this within CI and/or MySQL?

Parag Tyagi · Accepted Answer · 2015-02-08 17:00:02Z

2

No, there isn't any option in CI Active Records to achieve that.
You have to loop your result.

<?php

$result = array(
    array(
            'task_id' => 1,
            'name' => 'do dishes',
            'user' => 'bob'
        ),
    array(
            'task_id' => 1,
            'name' => 'do dishes',
            'user' => 'liam'
        ),
    array(
            'task_id' => 2,
            'name' => 'vacuum',
            'user' => 'liam'
        ),
    array(
            'task_id' => 3,
            'name' => 'Take out thrash',
            'user' => 'liam'
        ),
    array(
            'task_id' => 3,
            'name' => 'Take out thrash',
            'user' => 'bob'
        ),
    );


function get_arrayvalues_bykeyvalue($array, $key, $key2, $v2)
{
    $ret = array();
    foreach($array as $arr)
    {
        foreach($arr as $k => $v)
        {
            if($arr[$key2] == $v2)
            {
                if($k == $key)
                    $ret[] = $v;   
            }
        }
    }
    $u = array_unique($ret);
    return (sizeof($u) == 1) ? $u[0] : $u;
}

$res = array();
foreach($result as $arr)
{
    foreach($arr as $k => $v)
    {
        if($k == 'user')
            $res[$arr['task_id']][$k] = get_arrayvalues_bykeyvalue($result, $k, 'task_id', $arr['task_id']);
        else
            $res[$arr['task_id']][$k] = $v;
    }
}

print_r($res);

Output:

Array
(
    [1] => Array
        (
            [task_id] => 1
            [name] => do dishes
            [user] => Array
                (
                    [0] => bob
                    [1] => liam
                )

        )

    [2] => Array
        (
            [task_id] => 2
            [name] => vacuum
            [user] => liam
        )

    [3] => Array
        (
            [task_id] => 3
            [name] => Take out thrash
            [user] => Array
                (
                    [0] => liam
                    [1] => bob
                )

        )

)

Demo:
http://3v4l.org/2E9nP

edited Feb 8, 2015 at 17:00

answered Feb 8, 2015 at 15:19

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

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

3 Comments

Fjarlaegur Over a year ago

This would still return 2 rows. As i tried to use this in my project and directly in MySQL workbench. That is not what i want. Am i being unclear in my problem? Because then i will try to explain it better.

Parag Tyagi Over a year ago

Apologize, didn't read the whole question. Please check the edited answer. By the way found your question interesting +1

Fjarlaegur Over a year ago

Still not what i want to achieve. When i add more arrays like array( 'task_id' => 2, 'name' => 'Vacuum', 'user' => 'bob' ), This will be the output: Array ( [task_id] => Array ( [0] => 1 [2] => 2 ) [name] => Array ( [0] => do dishes [2] => Vacuum ) [user] => Array ( [0] => bob [1] => liam ) ) I want each task to be in a different Array, where the users will be an array INSIDE the array of the task. Thanks tho!

Shaiful Islam · Accepted Answer · 2015-02-08 17:16:32Z

0

Lets assume your data has inside $results;
First make a function which will check task id already inside the new result array.if exists return the index else return flase

function get_index($array,$task_id)
{
    foreach($array as $index=>$a)
    {
        if($a['task_id']==$task_id)
        {
            return $index;
        }
    }
    return false;
}

Now write follwing code to get your desired result inside $new_result

$new_result=array();//this will contains your desired result
foreach($results as $key=>$result)
{
    $index=get_index($new_result,$result['task_id']);

    if($index===false)
    {
        $new_result[]=array('task_id'=>$result['task_id'],'name'=>$result['name'],'users'=>array($result['user']));
    }
    else
    {
        $new_result[$index]['users'][]=$result['user'];
    }
}
print_r($new_result);//this will output your desired result.

edited Feb 8, 2015 at 17:16

answered Feb 8, 2015 at 16:58

Shaiful Islam

7,12612 gold badges41 silver badges59 bronze badges

2 Comments

Fjarlaegur Over a year ago

Fixed my problem! Thanks! Only thing i have to mark: I am using this code in my Model. (I guess i shouldnt) but also in a controller i guess you should put "$this->" in front of the function you are calling. Otherwise you will get errors.

Shaiful Islam Over a year ago

you can keep get_index function inside a helper so you don't need to use $this. You can keep this function inside model or controller. In that case you need to call it proper way.

AdrienXL · Accepted Answer · 2015-02-08 17:56:10Z

0

I don't have your model, but how I see it, you can use group_concat

Model :

$this->db->select('t.id_task, t.name, GROUP_CONCAT(u.name SEPARATOR "-") as users') 
         ->from('tasks t')
         ->join('users_task ut', 't.id_task = ut.id_taks')
         ->join('user u', 'u.id_user = ut.id_user')
         ->group_by(t.id_task);
$query = $this->db->get(); 
return $query->result();

/*Result expected :
 Array
(
[1] => Array
    (
        [task_id] => 1
        [name] => do dishes
        [users] => "John-Peter"

    )

[2] => Array
    (
        [task_id] => 2
        [name] => vacuum
        [users] => "Mary-Bob"
    )
) */

After that, a simple explode should do the trick in your controller.

Controller :

$tasks = $this->my_model->getTasks();
foreach($tasks as $t)
{
    $t->users = explode("-", $t->users);
}

edited Feb 8, 2015 at 17:56

answered Feb 8, 2015 at 17:48

AdrienXL

3,0083 gold badges25 silver badges40 bronze badges

1 Comment

mickmackusa Mar 2 at 22:30

The downsides of this suggestion: 1. different database dialects may have different syntax for GROUP_CONCAT. 2. Values being joined together may contain the delimiting character -- which will lead to corrupted data when they are later exploded. 3. GROUP_CONCAT() may truncate values if the total string is too long.

mickmackusa · Accepted Answer · 2025-03-02 21:31:27Z

It probably makes sense to rename the user column to users when it is expected to contain one or more values. I prefer to push a new reference into the result array for each unique task id while looping. If a task_id has been encountered before, just push the new user value into the users column of the related reference. Demo

$result = [];
foreach ($array as $row) {
    extract($row);
    if (!isset($ref[$task_id])) {
        $ref[$task_id] = ['task_id' => $task_id, 'name' => $name, 'users' => []];
        $result[] =& $ref[$task_id];
    }
    $ref[$task_id]['users'][] = $user;
}
var_export($result);

I do not recommend using nested loops to perform value-comparisons -- this will be less direct/performant.

Collectives™ on Stack Overflow

Group rows of a 2d array by a column and create subarray elements from another column

4 Answers 4

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related