CodeIgniter查询不会返回正确的数据

Question

So I have two tables customers and agenda

What I'm trying to get is all customers for user_id and their next appointment.

This is how far I got , but this keeps returning a wrong date

        $today = date("Y-m-d");

        $this->db->select('c.firstname,c.lastname,a.date as next_appointment');
        $this->db->from('customers c');
        $this->db->join('agenda a','a.customer_id = c.customer_id AND a.date > '.$today.'','left');
        $this->db->where('c.user_id', $user_id, FALSE);
        $this->db->group_by('c.customer_id');
        $this->db->order_by('c.firstname', 'asc');

        $Q = $this->db->get();

        die(json_encode($Q->result_array()));

Here is a screenshot of the results

All these dates should be in the future otherways it should return NULL

Note : another small thing that might be causing this is that no matter how I join "left" "right" "inner" It keeps returning exactly the same results.

Thanks in advance.

Answer 1

You need to quote your date in your query.

$this->db->join('agenda a', 'a.customer_id = c.customer_id AND a.date > "'.$today.'"', 'left');

Otherwise your date will be interpreted as a number, such as 2017-03-28 or 1986 (since that's what you get when you subtract).

You can also do:

$this->db->join('agenda a', "a.customer_id = c.customer_id AND a.date > '{$today}'", 'left');