I have here my sample query using codeigniter. I'm using this to get data satisfying my where condition.
MODEL:
function quarterly1_sales($year_date, $q1_start)
{
$where = array(
'status' => 'purchased',
'payment.paymentdate >=' => $q1_start,
'payment.paymentdate <=' => 'DATE_ADD('.$q1_start.',INTERVAL 1 QUARTER)'
);
return $this->db
->select('COUNT(payment.keyid) AS rec_count')
->select('product_key.client_name, product_key.contact_email, product_key.status, product_key.id, payment.paymentdate, (payment.id) as pid,payment.subscription_type')
->from('product_key')
->where($where)
->join('payment', 'payment.keyid=product_key.id', 'left outer')
->group_by('product_key.id')
->get()
->result();
}
My problem is, there shouldn't be any result since my data starts with 2012 and 2013 yet it outputs everything.
Codeigniter's active record class automagically escapes everything passed to the where()
function. Because of this your call to the MySQL function DATE_ADD()
is being passed in as a string and treated literally by MySQL instead of being evaluated. To fix this, move that where clause to a separate function call and set the third parameter to FALSE, turning off the escaping.
$where = array(
'status' => 'purchased',
'payment.paymentdate >=' => $q1_start);
return $this->db
->select('COUNT(payment.keyid) AS rec_count')
->select('product_key.client_name, product_key.contact_email, product_key.status, product_key.id, payment.paymentdate, (payment.id) as pid,payment.subscription_type')
->from('product_key')
->where($where)
->where('payment.paymentdate <=', 'DATE_ADD('.$this->db->escape($q1_start).',INTERVAL 1 QUARTER)', FALSE)
->join('payment', 'payment.keyid=product_key.id', 'left outer')
->group_by('product_key.id')
->get()
->result();