Create a grouped multidimensional array with nested subarrays from a CodeIgniter query result

Question

Using this query, I am getting this array as a result, but some rows are repeating with some distinct columns. I want to take those distinct columns in to a separate array as a multidimensional array of the parent array. I tried to do it with a Mysql group_by distinct clause, but I couldn't do it. I think best way is to make it by PHP.

$query = "
SELECT
    a.emp_nmbr, a.emp_frstNm,
    a.emp_lstNm, a.emp_lclFrn,
    b.emp_dsgntn, b.emp_dprtmnt,
    b.emp_allwncs, b.emp_bscSlry,
    b.emp_sts, c.emp_lvsCtgry,
    c.emp_tknFrm, c.emp_tknTo
FROM
    employee a
LEFT JOIN
    emp_details b
        ON a.emp_nmbr = b.emp_nmbr
LEFT JOIN
    leaves_details c
        ON a.emp_nmbr = c.emp_nmbr            
WHERE
    emp_lclFrn = 0
LIMIT
    $page, $perPage
";

CodeIgniter model function to execute query:

function count_and_data($query)
{
    $query = $this->db->query($query);
    $result = $query->result();
    $count = $query->num_rows();
    $data['count'] = $count;
    $data['rows'] = $result;
    return $data;
}

The result array I am getting

What I am Expecting is

jogesh_pi · Accepted Answer · 2014-12-22 07:24:13Z

1

This is a quick flow that helps you to achieve your requirenment.

// collect all the unique data here
$holder = array();

foreach($data['rows'] as $data) {

    // check with emp_nmbr = ophl3
    if( ( $key = _finder($data->emp_nmbr, $holder) ) === false ) {
        $holder[] = array(
            'emp_nmbr'=>$data->emp_nmbr, 
            ..........
            'emp_lvData'=>array(
                array('emp_lvsCtgry'=>$data->emp_lvsCtgry)
                ..........
            )
        );
    }
    else {
        // emp_nmbr = ophl3 found now Merge Data Here.. 
        if( ! empty( $data->emp_lvsCtgry ) ) {
            $holder[$key]['emp_lvData'] = 
                 array_merge( 
                     $holder[$key]['emp_lvData'], 
                     array('emp_lvsCtgry'=>$data->emp_lvsCtgry));
        }   
    }

}

// Search Value in Muliti-dimentional array
function _finder($key, $array) {

    if( ! is_array($array) || count($array) == 0 ) return false;

    foreach( $array as $k=>$arr ){
         if( in_array( $key, $arr ) ) {
             return $key;
         }
    }
    return false;
}

Where $holder holds all your unique array data.

edited Dec 22, 2014 at 7:24

answered Dec 22, 2014 at 7:02

jogesh_pi

9,7745 gold badges40 silver badges65 bronze badges

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

4 Comments

Yasitha Over a year ago

I feel this will do the work but. still I am getting some errors

Yasitha Over a year ago

array_merge(): Argument #1 is not an array (this error two times) Undefined index: ophl3 both errors are in this line $holder[$key]['emp_lvData'] = array_merge($holder[$key]['emp_lvData'], array('' => $data->emp_lvsCtgry));

jogesh_pi Over a year ago

@Yasitha you have to check the array first, because in some cases you don't have value in emp_lvsCtgry

Yasitha Over a year ago

Sorry for disturbing you. I checked your updated code. it is returning only emp_lvsCtgry for each employee. if I tried to add other details to $holder array then it is again giving the same three errors as above. I change your code from $holder[] = array( 'emp_lvData' => array( array('emp_lvsCtgry' => $data->emp_lvsCtgry) ) to $holder[] = array( 'emp_nmbr' => $data->emp_nmbr, 'emp_lvData' => array( array('emp_lvsCtgry' => $data->emp_lvsCtgry) )

mickmackusa · Accepted Answer · 2025-12-12 11:00:31Z

Rather than fumbling with grouping, and keeping track of unique nmbr values, you can let MySQL do that job. Because databases can only return 2d payloads, you will either need to loop over each object in the result array and json_decode the accumulated leaves data in the model method or ensure that that process is done in another layer.

$query = <<<SQL
SELECT 
    emp.emp_nmbr,
    emp.emp_frstNm,
    emp.emp_lstNm,
    emp.emp_lclFrn,
    det.emp_dsgntn,
    det.emp_dprtmnt,
    det.emp_allwncs,
    det.emp_bscSlry,
    det.emp_sts,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'emp_lvsCtgry', lvs.emp_lvsCtgry,
            'emp_tknFrm', lvs.emp_tknFrm,
            'emp_tknTo', lvs.emp_tknTo
        )
    ) emp_lvData
FROM employee emp
LEFT JOIN emp_details det USING (emp_nmbr)
LEFT JOIN leaves_details lvs USING (emp_nmbr)
WHERE emp.emp_lclFrn = 0
GROUP BY 
    emp.emp_nmbr,
    emp.emp_frstNm,
    emp.emp_lstNm,
    emp.emp_lclFrn,
    det.emp_dsgntn,
    det.emp_dprtmnt,
    det.emp_allwncs,
    det.emp_bscSlry,
    det.emp_sts
LIMIT $page, $perPage
SQL;

Related advice:

Collectives™ on Stack Overflow

Create a grouped multidimensional array with nested subarrays from a CodeIgniter query result

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related