如何在PHP CodeIgniter中将日期作为参数传递给MySQL存储过程？

Question

我正在尝试在我的Web应用程序中创建一个日期范围过滤器。为此，我使用日期选择器获取输入，并将其传递给我的控制器模型。当我将其回显时，它会给出我从日期选择器中选择的正确日期值。但是当我运行代码时，它会给出空白输出。我想知道我的代码有什么问题。

以下是我的SP代码：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testDefects`(IN `startdate` DATE, IN `enddate` DATE)
    NO SQL
    DETERMINISTIC
BEGIN
SELECT `defect_id`, `user_id`, `defect_rooms`, `defect_start_date`, `defect_subject`, `defect_responsibility`, `defect_time_frame`, `defect_followup_date`, `defect_assign_to`, `defect_notes`, `defect_details`, `defect_status`, `defect_updated_date` FROM `tbl_defects` WHERE defect_followup_date BETWEEN startdate AND enddate;
END$$
DELIMITER ;

Here is my Controller code:

public function DefectFilterTest()
    {
        # code...
        $user = $this->ion_auth->user()->row();
        $data['username'] = $user->username;

        $data['user_id'] = $user->id;
        $user_id = $user->id;
        $data['groupId'] = $this->l->groupId($user_id);
        $data['group'] = $data['groupId']['0']->group_id;
        $data['title'] = 'Property Defects List';

        $startdate = $this->input->post('fromdate');
        $enddate = $this->input->post('todate');

        if($startdate == ''){
            $startdate ='1900-01-01';
        }
        if($enddate == ''){
            $enddate ='2100-01-01';
        }
        echo $startdate. "<br>";
        echo $enddate. "<br>";

        $data['defectslist'] = $this->p->testDefects($startdate, $enddate);

        echo "<pre>";
        print_r($data['defectslist']);
        echo "</pre>";
        exit();

        $this->load->view('template/header', $data); 
        $this->load->view('Property/defectList', $data); 
        $this->load->view('template/footer'); 
    }

Here is my Model code:

public function testDefects($startdate, $enddate)
    {
        # code...   testDefects
        $query = $this->db->query("call testDefects($startdate, $enddate)");

        if ($query->num_rows()) {
            $data = $query->result();
            $query->next_result(); 
            $query->free_result();
            return $data;
        }else{
            return false;
        }
    }

我已经谷歌搜索了，但是我卡住了，欢迎任何帮助，提前致谢。

- Ganesh Aher

---

日期实际上是字符串，因此在SQL查询中应该加引号或者更好的方法是使用预处理语句，并将其作为参数传递。 - Professor Abronsius

Answer 1

试试这个：

# code...   testDefects
$query = $this->db->query("call testDefects('$startdate', '$enddate')");

- Salim Ibrohimi