Codeigniter group by and create multidimensional array

Question

I've got a function in my model:

public function get_job($lsnumber = FALSE) {        
        $this->db->join('administrator', 'job.idadministrator = administrator.idadministrator');
        $this->db->join('artwork', 'job.idjob = artwork.idjob');

        if($lsnumber === FALSE) {
            $query = $this->db->get('job');
            return $query->result_array();
        }
    }

Returning this lets me generate a row for each entry in the database using a foreach loop in my view.

Sometimes these rows will have things in common, for example the idjob field as referenced in the join rule.

At the moment, I'm getting an output (from print_r) like this:

Array
(
    [idjob] => 1
    [lsnumber] => 12345
    [custname] => Scott Brown (Customer)
    [custemail] => [email protected]
    [custcompany] => Customer
    [idadministrator] => 1
    [complete] => 0
    [administratorname] => Scott Brown (Administrator)
    [administratoremail] => [email protected]
    [administratorjob] => Job
    [administratorphone] => 01234 567890
    [idartwork] => 1
    [filename] => gb-usb1.jpg
    [productname] => Bespoke USB Drive
    [revision] => 0
    [status] => N
)

Array
(
    [idjob] => 1
    [lsnumber] => 12345
    [custname] => Scott Brown (Customer)
    [custemail] => [email protected]
    [custcompany] => LSi (Customer)
    [idadministrator] => 1
    [complete] => 0
    [administratorname] => Scott Brown (Administrator)
    [administratoremail] => [email protected]
    [administratorjob] => Job
    [administratorphone] => 01234 567890
    [idartwork] => 2
    [filename] => pa17.jpg
    [productname] => Notebooks
    [revision] => 0
    [status] => Y
)

However, I'd like the array to be grouped and multidimensional, something like this:

Array
(
    [idjob] => 1
    [lsnumber] => 12345
    [custname] => Scott Brown (Customer)
    [custemail] => [email protected]
    [custcompany] => Customer
    [idadministrator] => 1
    [complete] => 0
    [administratorname] => Scott Brown (Administrator)
    [administratoremail] => [email protected]
    [administratorjob] => Job
    [administratorphone] => 01234 567890
    [artwork][0] => Array
        (   
            [idartwork] => 1
            [filename] => gb-usb1.jpg
            [productname] => Bespoke USB Drive
            [revision] => 0
            [status] => N
        )
    [artwork][1] => Array
        (   
            [idartwork] => 2
            [filename] => pa17.jpg
            [productname] => Notebooks
            [revision] => 0
            [status] => Y
        )
)

I want to be able to group it by the idjob and build a sub-array of all the artworks joined to it.

I've tried all kinds of thing: array_chunk(), group by, all sorts.

Nil'z · Accepted Answer · 2013-09-11 15:43:30Z

-1

Try this, you may have to change it slightly:

function get_job(){
    $data   = array();
    $data   = $this->db->get('job')->result_array();
    foreach( $data as $key=>$each ){
        $data[$key]              = $this->db->where('idadministrator', $each['idadministrator'])->get('administrator')->row_array();
        $data[$key]['artwork']   = $this->db->where('idjob', $each['idjob'])->get('artwork')->result_array();  
    }
    return $data;
}

answered Sep 11, 2013 at 15:43

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

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

3 Comments

user527892 Over a year ago

This is great, thank you. One more thing though, how can I tweak it to keep the idjob, custname etc in the array?

user527892 Over a year ago

Don't worry, I've figured that out. Changed the first $data[$key] to the job information and added another $key for administrator. Thanks for your help and, what's better, it almost all makes sense to me :)

fishbone Over a year ago

Bad idea. You run a query for each job. Now consider one query takes 1ms and there are 10000 jobs => your page needs 10 seconds to load. Your application is not very scalable (= performance tremendously depends on the amount of data). With the join-approach you have a single query being executed

Collectives™ on Stack Overflow

Codeigniter group by and create multidimensional array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related