Help with Select, Join across multiple tables with CodeIgniter - MYSQL

Question

I am trying to make a query in Codeigniter that will allow a user to retrieve a record they submitted in the past to my database.

This is my schema:

https://i.sstatic.net/zSHSQ.png

enter image description here

I am having trouble figuring out what I need to do for some of my tables

For example 1:M or M:M

1 procedure has 1 or many procedure_event
a procedure_event may or may not have use resources
1 procedure has 1 or many staff

I think I need to use Select, Joins for this and I have got some of it completed but for the Many to Many relationships and linked tables I am confused how to do them.

This is what I have got so far in terms of code but it is not returning any rows so I think it is wrong:

    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
        $criteria = array 
        (
            'procedure_id' => $record_id
        );

        //this selects the columns from procedure table for use in joins 
        $this->db->select('procedure.procedure_id, procedure.patient_id, procedure.department_id, procedure.name_id , procedure.dosage_id');
        //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', 'inner');

        //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', 'inner');

        //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', 'inner');

        //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', 'inner');

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

        /*
        Code for other tables:
        I need help with

        procedure_event, staff, resources, hr

        */

        //this part I think is wrong
        $result = $this->db->get();


        //
        if ($result->num_rows >0)
        {
            echo "There is Data!";

        }

        else
        {
            echo "No Data!";

        }

}

I get back a message saying "No Records" however there is data in my tables etc:

So my query so far must be wrong.

Thanks for your time!

I'd use left join if yuo're planing to have relation with zero elements on one hand — Dalen
– Dalen, Commented Aug 1, 2011 at 13:44
yes, left join means that you want to have the left hand table data even if it does not join with the right hand table, having said that the row will have this structure: left_table.a,left_table.b,NULL,NULL — Dalen
– Dalen, Commented Aug 1, 2011 at 14:33

Robin Castlin · Accepted Answer · 2011-08-01 13:59:01Z

1

Firstly, I personally consider it easier to write as complex queries as this on in pure text, rather then using Active Records.

All your joins uses inner; that is that all rows need to find a partner in the other table. If any of these doesn't exist you get 0 rows. Consider using LEFT JOIN, as previously suggested.

Do all of these tables contain data at the moment?

Do need absolutely a partner row in everyone of these tables, or do you count on cases where rows are missing?

answered Aug 1, 2011 at 13:59

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

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

3 Comments

sqlmole Over a year ago

Hi thanks Robin, some records wont contain data in some tables e.g. sometime a procedure does not have a dosage so the dosage table has no data, each procedure must have a minimum of one staff row and one event row

Robin Castlin Over a year ago

Try for instance to change: $this->db->join('department', 'department.department_id = procedure.department_id', 'inner'); To: $this->db->join('department', 'department.department_id = procedure.department_id', 'left'); That is change 'inner' to 'left' on the tables which you know there might be no connection at.

sqlmole Over a year ago

Thanks alot Robin! It works now it is returning a matching record to the id I want :) Now how should I take my linked tables and the Many to Many relationship tables e.g. procedure_event -> resources? Thanks again

Collectives™ on Stack Overflow

Help with Select, Join across multiple tables with CodeIgniter - MYSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related