使用SQL获取空记录 [英] 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;
}

如果我选择3个框并提交表单，并且记录仅存在2个框，则会返回类似的内容

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] => 
    )

谢谢

推荐答案

为什么不只计算整个记录?

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;
}

尽管我不理解您的group_by子句...

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

这篇关于使用SQL获取空记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！