Codeigniter not showing proper results for array fetched by MySQL query.

Question

I am working with Codeigniter with mysql databases, I have a model function

public function get_all_data(){
    $this->output->enable_profiler(TRUE);
    $years = $this->input->post('year');
    $months = $this->input->post('month');
    $exec_ids = $this->input->post('exec_id');
    $query = $this->db->query("SELECT * FROM client_booking AS cb
        RIGHT OUTER JOIN executive_client_unit_relation AS ecur ON cb.id = ecur.booking_id
        LEFT OUTER JOIN new_invoice ON cb.id = new_invoice.booking_id
        WHERE ecur.executive_id IN (" . implode(',', $exec_ids) . ") AND MONTH(cb.booking_date) IN (" . implode(',', $months) . ") AND YEAR(cb.booking_date) IN (" . implode(',', $years) . ")");
    return $query->result();
}

The sql generated by this is (as seen in the profiler) :

SELECT * FROM client_booking AS cb 
        RIGHT OUTER JOIN executive_client_unit_relation AS ecur ON cb.id = ecur.booking_id 
        LEFT OUTER JOIN new_invoice ON cb.id = new_invoice.booking_id 
        WHERE ecur.executive_id IN (4,5,6) AND MONTH(cb.booking_date) IN (1,2,3,4,5) AND YEAR(cb.booking_date) IN (2013,2014)

My problem is that the booking_id value is set to NULL when I var_dump() the result of this query. On the other hand, when I run the SQL in my phpmyadmin, everything goes well and I can see the booking_id

PS: client_booking table's primary key is id which is the booking_id for all the other tables.

Can anyone help me to resolve this?

M Khalid Junaid · Accepted Answer · 2014-05-13 10:55:47Z

1

You are using LEFT/RIGHT joins so when there is no association found between your tables a null row will be returned ,second thing is your tables new_invoice and executive_client_unit_relation both have common names for the column name booking_id and in your select statement you are doing select * so for the columns with same name i guess the last one is picked which is null,for the solution you either you select only needed columns not all like SELECT cb.* or either the columns have same name with your query table then specify them individually with the alias you wish to provide but in the end of select statement in query like SELECT *,cb.id AS booking_id,....

answered May 13, 2014 at 10:55

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

Collectives™ on Stack Overflow

Codeigniter not showing proper results for array fetched by MySQL query.

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related