'CodeIgniter 3 query not returning null or 0 when records are not available

CI3.1.11 query not working Am trying to filter records between two dates. You can check my code sample here https://pastebin.com/5UhnAG6e instead of giving me 0 when the date range is not available in DB it pulls records from another year. when i run the same query using raw query in SQLyog it works just fine what could be the problem? $query = $this->db->select_sum('sold_price')->from('tbl_sales')->where('category_ID', $caid,'sold_date >=',$prev_year_start_month,'sold_date <=',$prev_year_end_month)->group_by('category_ID')->get()->row()->sold_price;

phpcodeigniter-3mariadb-10.4


Solution 1:[1]

Please restructure your query as:  

  $query = $this->db->select_sum('sold_price')
                      ->from('tbl_sales')
                      ->where('category_ID', $caid)
                      ->where('sold_date >=',$prev_year_start_month)
                      ->where('sold_date <=',$prev_year_end_month)
                      ->group_by('category_ID')
                      ->get()
                      ->row()
                      ->sold_price;
    or you have to use:
    $query = $this->db->select_sum('sold_price')
                      ->from('tbl_sales')
                      ->where("category_ID= $caid AND sold_date >= '$prev_year_start_month' AND sold_date <='$prev_year_end_month'")
                      ->group_by('category_ID')
                      ->get()
                      ->row()
                      ->sold_price;

Solution 2:[2]

Your Query:

Associative array method:

$conditions = array(  'category_ID' => $caid,
                      'sold_date >=' => $prev_year_start_month,
                      'sold_date <=' => $prev_year_end_month
                    );
$query = $this->db->select_sum('sold_price')
                  ->from('tbl_sales')
                  ->where($conditions);
                  ->group_by('category_ID')
                  ->get()
                  ->row()
                  ->sold_price;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nik
Solution 2 Shimanto

Related Questions