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?

Answer 1

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.