Codeigniter returns Null from database a field which is not null

Question

I want to return some data from my database with Codeigniter. The query is a join from all related tables. Here is teh diagram:

The join query from the model:

public function combine_all_data_related($hostname){
        $this->db->select('*');
        $this->db->from('workstations w');
        $this->db->join('occupations o', 'w.occupation_id = o.occupation_id', 'left');
        $this->db->join('departments d', 'd.department_id = o.department_id', 'left');
        $this->db->join('workstation_configuration wc', 'wc.service_tag = w.service_tag', 'left');
        $this->db->join('workstation_models wm', 'wm.workstation_model_id = wc.workstation_model_id', 'left');
        $this->db->join('workstation_types wt', 'wt.workstation_type_id = wm.workstation_type_id', 'left');
        $this->db->join('users u', 'u.occupation_id = o.occupation_id', 'left');
        $this->db->join('phone_numbers pn', 'pn.fmid = u.fmid', 'left');
        $this->db->join('phones p', 'p.phone_number_id = pn.phone_number_id', 'left');
        $this->db->join('phone_brands pb', 'pb.phone_brand_id = p.phone_brand_id', 'left');
        $this->db->where("w.hostname = '" . $hostname . "'");

        $query = $this->db->get();

        return $return = $query->result();
    }

In the database, I have this record:

And in-browser here are the var_dumps results SQL statement and returned data. The records that are not OK acquisition date, latest edit, and status.

Try this echo $this->db->last_query(); after $query statement — Jaymin
– Jaymin, Commented Jul 18, 2019 at 9:09
the first paragraph from the last image is the print_r($this->db->last_query()); — Dragos Jianu
– Dragos Jianu, Commented Jul 18, 2019 at 9:15
You have aquisition_date fields in workstations and phones tables, because phones table is joined later in the query, it's value overrides the first one, give different aliases to those fields. — Naktibalda
– Naktibalda, Commented Jul 18, 2019 at 9:30
please show the last sql dump. It could be null because of LEFT JOIN. — dodzb
– dodzb, Commented Jul 18, 2019 at 10:02

Elena Vasilenko · Accepted Answer · 2019-07-19 17:17:19Z

You make SELECT * - select all columns from all specified tables. And in your tables some fields have the same names ('acquisition_date', 'latest_edit', 'status' - there are in 'phones' and 'workstations'). Therefore, you have an unpredictable result. Perhaps, phpmyadmin and Codeigniter use different drivers. In any case, using just SELECT * is very unreliable.

There are 2 solutions:

Instead of $this->db->select('*'); use $this->db->select('explicitly list all need fields', NULL);.

For example:

$this->db->select('w.hostname ..., w.acquisition_date AS ws_acquisition_date ..., o.occupation_id ..., p.acquisition_date AS ph_acquisition_date ...', NULL);

Or at least for the fields of the same name in the request to explicitly specify the name-aliases:

$this->db->select('*, w.acquisition_date AS ws_acquisition_date, p.acquisition_date AS ph_acquisition_date, w.status AS ws_status, w.latest_edit AS ws_latest_edit, p.latest_edit AS ph_latest_edit', NULL);

And in PHP, refer to these fields as:

$return->ws_acquisition_date
$return->ph_acquisition_date
$return->ws_status
...

Or rename the fields so that the names do not repeat.

I recommend the first solution.

Collectives™ on Stack Overflow

Codeigniter returns Null from database a field which is not null

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