php - 如何在 codeigniter 中根据日期明智地从 3 个表中获取数据？

Question

public function St_statement() {

    $startdate = $this->input->post('SDate');
    $enddate = $this->input->post('EDate');
    $date = str_replace('/', '-', $startdate);
    $newDate = date("Y-m-d", strtotime($date));
    $date2 = str_replace('/', '-', $enddate);
    $newDate2 = date("Y-m-d", strtotime($date2));
    $data['startdate'] = $startdate;
    $data['enddate'] = $enddate;

    if ($this->input->post('all')) {
        $query1 = $this->db->query("SELECT `pgroup`,`Item`, SUM(`Stock`) AS `Stock`,SUM(`quantity`) AS `quantity`,SUM(`Qty`) AS `Qty`FROM `opstock` LEFT OUTER JOIN `salesitem` ON `salesitem`.`Prdtname` = `opstock`.`Item` LEFT OUTER JOIN `itemmaster` ON `itemmaster`.`itemname` = `opstock`.`Item` LEFT OUTER JOIN `pgroup` ON `pgroup`.`pgroupid` = `itemmaster`.`catcode` LEFT OUTER JOIN `purchaseitem` ON `purchaseitem`.`Prdtname` = `opstock`.`Item` WHERE  billdte >= '$newDate' AND billdte <= '$newDate2' AND billdate >= '$newDate' AND billdate <= '$newDate2' GROUP BY `Item` ORDER BY `pgroup` ASC ")->result_array();

        $data['query'] = $query1;

        $this->load->view('Inventory/St_Stmt', $data);

    }

    if ($this->input->post('selected')) {
        if ($name = $this->input->post('businessType')) {
            $query1 = $this->db->query("SELECT `pgroup`,`Item`, SUM(`Stock`) AS `Stock`,SUM(`quantity`) AS `quantity`,SUM(`Qty`) AS `Qty`FROM `opstock` LEFT OUTER JOIN `salesitem` ON `salesitem`.`Prdtname` = `opstock`.`Item` LEFT OUTER JOIN `itemmaster` ON `itemmaster`.`itemname` = `opstock`.`Item` LEFT OUTER JOIN `pgroup` ON `pgroup`.`pgroupid` = `itemmaster`.`catcode` LEFT OUTER JOIN `purchaseitem` ON `purchaseitem`.`Prdtname` = `opstock`.`Item` WHERE `pgroup` = '$name' AND billdte >= '$newDate' AND billdte <= '$newDate2' AND billdate >= '$newDate' AND billdate <= '$newDate2' GROUP BY `Item` ORDER BY `pgroup` ASC")->result_array();

            $data['query'] = $query1;

            $this->load->view('Inventory/St_Stmt', $data);

        }

    }
}

这是一个 Controller 代码......

我的问题是如何在 codeiginter 中使用日期方式从四个表中获取数据。当我使用 where 条件时，如果所有表都存在，它只会获取数据，否则它不会显示该项目。

这张图片使用了where condition.它只显示所有三个表都有相同的项目。

这张图没有使用where条件，但是它显示了所有数据是否存在。

请帮忙解决这个问题

Answer 1

将 where 条件语句移动到“ON”它将起作用。

例如。

LEFT OUTER JOIN `salesitem` ON `salesitem`.`Prdtname` = `opstock`.`Item` AND billdte >= '$newDate' AND billdte <= '$newDate2' AND billdate >= '$newDate' AND billdate <= '$newDate2' 

如果它在 where 中，这对于上述所有条件都是通用的。这就是为什么你没有得到任何结果。因此，将其从 where 条件中删除并将该条件移至“ON”语句。