php - 在 MySQL 查询中的 where 子句中传递变量 PHP CodeIgniter

Question

我使用 codeigniter 版本 2。

如何从查询内的 where 子句传递 $idCabang 和 $tahun？

public function getListPendapatan($idCabang,$tahun)
{
    $idCabang=$this->input->post('id_cabang');
    $tahun=$this->input->post('tahun');

    $query = '
    SELECT `a`.`NAMA_BULAN` AS `Bulan`,
    CASE WHEN `b`.`TOTAL_STLH_DISC` IS NULL
    THEN 0
    ELSE `b`.`TOTAL_STLH_DISC`
    END AS `Total_Pendapatan`
    FROM `tbl_bulan` AS `a`
        LEFT JOIN (
        SELECT MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`) AS `TGL_TRANSAKSI` , SUM(`tbl_transaksi_jual`.`TOTAL_STLH_DISC`) AS `TOTAL_STLH_DISC`
        FROM `tbl_transaksi_jual`
        WHERE `tbl_transaksi_jual`.`ID_CABANG`="'.$idCabang.'" AND 
            YEAR(`tbl_transaksi_jual`.`TGL_TRANSAKSI`)= "'.$tahun.'"
        GROUP BY MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`)
        ) AS `b` ON `a`.ID_BULAN = `b`.`TGL_TRANSAKSI`
    ORDER BY `a`.ID_BULAN ASC';

    $result = $this->db->query($query, '');
    if($result -> num_rows()>0)
    {
        return $result->result();
    }
    else
    {
        return false;
    }

}

它不起作用。为什么？

我想在where子句中创建一个输入变量。

Answer 1

替换询问的串联并将数组放入 $this->db->query()...

这样做:

$query = '
    SELECT `a`.`NAMA_BULAN` AS `Bulan`,
    CASE WHEN `b`.`TOTAL_STLH_DISC` IS NULL
    THEN 0
    ELSE `b`.`TOTAL_STLH_DISC`
    END AS `Total_Pendapatan`
    FROM `tbl_bulan` AS `a`
        LEFT JOIN (
        SELECT MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`) AS `TGL_TRANSAKSI` , SUM(`tbl_transaksi_jual`.`TOTAL_STLH_DISC`) AS `TOTAL_STLH_DISC`
        FROM `tbl_transaksi_jual`
        WHERE `tbl_transaksi_jual`.`ID_CABANG`= ? AND 
            YEAR(`tbl_transaksi_jual`.`TGL_TRANSAKSI`)= ?
        GROUP BY MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`)
        ) AS `b` ON `a`.ID_BULAN = `b`.`TGL_TRANSAKSI`
    ORDER BY `a`.ID_BULAN ASC';

    $result = $this->db->query($query, array($idCabang, $tahun));