My query is duplicating results? MySQL / PHP/ Codeigniter

To make this a simple as possible to understand I am going to give you all the details I can so I can figure out this problem.

---

Okay so my problem is that my query that fetches me ONE record from my database is returning what appears to be duplicate values of the same record in multiple arrays. When I check my query result all my tables are duplicated and placed into arrays - one array for each duplicated record (or row).

The cause of this I think is my 1 --> Many relationship table (Procedure --> Procedure_event)

Here is my shema (you will see procedure and procedure_event) are 1: M relationship.

http://i.imgur.com/Dju0G.png

---

Here is my query result as you can see my tables (apart from procedure event) are repeated with the same data in each array. The quantity of arrays returned is dependant on how many procedure_event rows there are linked to procedure.

In this list there are three! --> http://开发者_如何学编程pastebin.com/pc53Pmgf

To illustrate to you what is happening here is a diagram:

Query Result:

procedure row x ---> procedure_event row x

procedure row x ---> procedure_event row x+1

procedure row x ---> procedure_event row x+1

What I want to returned is this (no duplication of other tables):

procedure row x ---> procedure_event x

           ---> procedure_event x+1

            ---> procedure_event x+1

---

Here is my query in Codeigniter Active Record

public function view_record($record_id)
        {
            //The criteria used for WHERE is a variable with array of procedure_id = record_id that is passed to function
            //e.g. Record id = 2419
            $criteria = array 
            (
                'procedure_id' => $record_id
            );

            //this selects the columns from procedure table for use in joins 
            $this->db->select('*');
            //this is the table I want to use the columns from
            $this->db->from ('procedure');

            //this joins the patient row that matches its ID to the patient ID in the procedure table
            $this->db->join('patient', 'patient.patient_id = procedure.patient_id', 'left');

            //this joins the department row that matches its ID to the patient ID in the procedure table
            $this->db->join('department', 'department.department_id = procedure.department_id', 'left');

            //this joins the procedure_name row that matches its ID to the patient ID in the procedure table
            $this->db->join('procedure_name', 'procedure_name.procedure_name_id = procedure.name_id', 'left');

            //this joins the dosage row that matches its ID to the patient ID in the procedure table]
            $this->db->join('dosage', 'dosage.dosage_id = procedure.dosage_id', 'left');

            //----->This is what is causing the problem -- try a different JOIN?
            $this->db->join('procedure_event', 'procedure.procedure_id = procedure_event.procedure_id' , 'left');

            //this selects the row in procedure table that matches the record number
            $this->db->where('procedure.procedure_id', $record_id);

            //gets all the data from the query
            $result = $this->db->get();


            //
            if ($result->num_rows >0)
            {
                return $result->result();
            }

            else
            {
                return FALSE;

            }


        }

I hope this is clear to everyone. I hope there is a way to solve this otherwise I am going to be stuck as I do not know how to deal with all this repeated data as it is messing up my foreach loops.

In the words of Princess Leiah

"Help me Obi'wan, your my only hope!"

Thanks!

---

So, if I understand you correctly, you want the procedure_event to be returned as a nested array? Is this right?

If so, I'm not sure if Codeigniter offers a way to do that directly. Your best bet is probably to process the arrays that you are currently getting using php, to put the data into the nested non-duplicate format that you want.

You could also break it down into 2 queries. One to get all the procedure data, using all the joins but leaving out the procedure_event joint. Then do a second query where you just join procedure to procedure_event. It would then be a simply matter to loop through the result and prepare the data the way you would like it.

---

you need to process the query result array in php like following.

$result = $this->db->get();
foreach($result as $key=>$value){
  $result_arr[$value->procedure_id] = $value;
}

and then

return $result_arr;