how to use optional parameter in codeigniter-CodePudding

I am trying to use optional parameter in CodeIgniter. My controller function is like below.

public function expenses($head = null, $date_to= null, $date_from =null)
    {
        $query_expenses =  $this->user_model->get_all_expenses($head,$date_to,$date_from);

        //more code
    }

My Model code is like below

public function get_all_expenses($head = null, $date_to= null, $date_from =null)
    {
        $array = array('cat_id ==' => $head, 'date >' => $date_from, 'date <' => $date_to);
        $this->db->select("*,category.name as cat_name,expense.created_at as created_at_expense");
        $this->db->from('expense')->where($array);

        //more code
    }

I am getting error like below.

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`NULL` AND `date` > AND `date` <' at line 5

SELECT *, `category`.`name` as `cat_name`, `expense`.`created_at` as `created_at_expense` FROM `expense` JOIN `category` ON `expense`.`cat_id` = `category`.`id` JOIN `users` ON `expense`.`user_id` = `users`.`id` WHERE `cat_id` = `IS` `NULL` AND `date` > AND `date` <

Filename: models/User_model.php

Line Number: 603

CodePudding user response：

Try this:

public function get_all_expenses($head = null, $date_to= null, $date_from =null)
{
    $filter = [];
    if( $head ) $filter[] = ['cat_id' => $head]; // == is default
    if( $date_to ) $filter[] = ["date <" => $date_to];
    if( $date_from ) $filter[] = ["date >" => $date_from];

    $this->db->select("*,category.name as cat_name,expense.created_at as created_at_expense");
    $this->db->from('expense')->where($filter);

    //more code
}

CodePudding user response：

You cannot use an array where for "IS NULL", you have to supply it as a whole string.

public function get_all_expenses($head = null, $date_to = null, $date_from = null)
{
    $this->db->select("*, category.name as cat_name, expense.created_at as created_at_expense");
    $this->db->from('expense');

    if (!empty($head)) {
        $this->db->where('cat_id', $head);
    } else {
        $this->db->where('cat_id IS NULL');
    }

    if (!empty($date_to) && !empty($date_from)) {
        $this->db->where('date >', $date_from);
        $this->db->where('date <', $date_to);
    }
    
    //more code
}