A better way of doing this

[eluser]joncarl[/eluser]
Hey All,

I'm writing an application to print invoicing for people receiving services in mental health and mental retardation in Pennsylvania. Now I'm running into a bit of a head scratcher, I have two tables a Assignment Table (the job we are doing) and a Contacts Table (the work performed) my final result needs to look a bit like this:

A Job LVS MH Fund 24.95 (One row with the job performed)
4/10/2009 10 Attended 24.95
4/11/2009 14 Absent-Medical
4/12/2009 4 Attended 24.95
.... (Multiple rows showing work performed for the job)
A Second Job LVS MH Fund 24.95 (Next Job)
4/10/2009 1 Attended 24.95
.... (Rinse and repeat until complete)

So I figured the best way to handle this was with a multidimension array, but for the life of me I can't figure out the best way to go about it that is the most efficient.

If anybody could throw a helpful hint my way that would be great!

Thanks!

[eluser]Colin Williams[/eluser]
Just need a JOIN query. Then you can either set up a multidim array from the result, or just check for changes in the job when you iterate over the set (but be sure the result is sorted first by job performed):

Code:

$cur_job = NULL;
foreach ($work as $item)
{
  if ($cur_job != $item->job_id)
  {
    print '<h3>'. $item->job_name .'</h3>';
  }
  print '<p>'. $item->title .'</p>';
  $cur_job = $item->job_id;
}

[eluser]joncarl[/eluser]
Thanks for the quick reply, I was successful in setting up a multidimensional array attached is a piece of my controller code I will eventually move the db calls out to the model but for testing purposes I put it all together for now:

Code:

$this->db->select('cis_assignment.assignment_id,cis_assignment.assignment_rate,cis_jobs.CIS_JOBNAME,cis_jobs.CIS_JOBLEVEL,cis_jobs.CIS_JOBCODE');
        $this->db->from('cis_assignment');
        $this->db->join('cis_jobs','cis_assignment.assignment_job = cis_jobs.CIS_JOBID','left');
        $this->db->where('cis_assignment.assignment_consumer',1);
        
        $query = $this->db->get();
        
        if($query->num_rows > 0)
        {
            $invoice = array();
            
            foreach($query->result() as $row)
            {
                $invoice[$row->assignment_id]['rate'] = $row->assignment_rate;
                $invoice[$row->assignment_id]['name'] = $row->CIS_JOBNAME;
                $invoice[$row->assignment_id]['joblevel'] = $row->CIS_JOBLEVEL;
                $invoice[$row->assignment_id]['jobcode'] = $row->CIS_JOBCODE;
                $invoice[$row->assignment_id]['contacts'] = $this->_getContacts();
                
            }
            
            $data['invoice']  = $invoice;
        }
        
        $this->load->view('reports/invoicetest_view',$data);
        
    }

a print_r of $invoice gives me a result of this:

Code:

Array (
    [1] => Array (
        [rate] => 23.04
        [name] => Unlicensed home and community habilitation
        [joblevel] => Basic support staff
        [jobcode] => W7057
        [contacts] => Array (
            [0] => Array (
                [contact_id] => 1
                [contact_date] => 2009-04-25 00:00:00
                [contact_assignment] => 1
                [contact_units] => 10
                [contact_attendance] => 1
                [contact_notes] =>
                [contact_staff] => 2
                [contact_created_by] => 1
                [contact_created_on] => 2009-04-23 00:00:00
                [contact_actual_units] => 10
                [contact_active] => 1
                .....
        )

Which contains all the info that I need, I'm now a bit stuck on how I would handle that in the view to get the final result I was looking for above. Eventually this is going to get throw to a pdf file as an invoice so I wanted to handle as much parsing of it on the controller side and not alot of inline code in the view other than parsing the array.

Thanks again for the help!