Create a multidimensional array from sql query result

Question

Using this query I am getting this array as a result. but some rows are repeating with some distinct columns. what I want to do is take those distinct columns in to a separate array as a multidimensional array of the parent array. I tried to do it with Mysql group_by distinct clause but I couldn't do it. I think best way is to make it by php. Please give me a little help with this thing. Thanks Myquery

$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

I think you can use a foreach loop the sql result and get you want. — lighter
– lighter, Commented Dec 22, 2014 at 6:44
First, I think your structure should be change something. And I have write a sample code to push same key value to a array. I hope that will help for your. gist.github.com/c11c1fbbd48321354d1f.git — lighter
– lighter, Commented Dec 22, 2014 at 6:52

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,7725 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) )

Collectives™ on Stack Overflow

Create a multidimensional array from sql query result

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related