CODEWITHSUNDEEP
phpmysqlsql
Zoha Ali Khan
Zoha Ali Khan

Reputation: 1699

Get the empty records using SQL

I have a form on which I select different boxes and for each box there exists some records i-e (the number of openings of the box) in the database but it might be possible that for a selected box no records exists ie the box is not opened even single time. Currently when I select different boxes and if records not exist for a box it does not return even the empty array I want to it return even the empty results for the selected boxes. how can I achieve that? My query is 

public function getBoxOpenings($boxes, $from_date, $to_date){
$query = $this->db->select('box_id, COUNT(box_id) AS Openings')
    ->from('mc_boxes_has_openings')
    ->where_in('box_id', $boxes)
    ->where('actiontime >=', $from_date)
    ->where('actiontime <=', $to_date)
    ->group_by('box_id')
    ->get();
$data = $query->result_array();
return $data;
}

Edit:

If I select 3 boxes and submit the form and records exists for only 2 boxes it returns something like this 

 Array
(
[0] => Array
    (
        [mc_boxes_idmc_boxes] => 12
        [location] => FRA-Air
        [Openings] => 1
    )

[1] => Array
    (
        [mc_boxes_idmc_boxes] => 14
        [location] => FRA-Hof
        [Openings] => 1
    )

)

How can I get all three records with one empty like this 

[2] => Array
    (
        [mc_boxes_idmc_boxes] => 16
        [location] => Test
        [Openings] => 
    )

Thanks

Upvotes: 1

Views: 147

Answers (1)

felixgaal
felixgaal

Reputation: 2423

Why don't just count whole records?

public function getBoxOpenings($boxes, $from_date, $to_date){
$query = $this->db->select('box_id, COUNT(*) AS Openings')
    ->from('mc_boxes_has_openings')
    ->where_in('box_id', $boxes)
    ->where('actiontime >=', $from_date)
    ->where('actiontime <=', $to_date)
    ->group_by('box_id')
    ->get();
$data = $query->result_array();
return $data;
}

Although I don't understand your group_by clause...

Upvotes: 1

Related Questions