I need to create a query in codeigniter as follows:
SELECT COUNT(*) AS `numrows` FROM `smslog_tbl` WHERE (lower(ResponseStatus) = 'submitted' OR lower(ResponseStatus) = 'inqueue') AND `SMSUsageID` = '1'
so i created a function in model as :
function submittedSMSCount($smsUsageID) {
$this->db->where('lower(ResponseStatus)', 'submitted');
$this->db->or_where('lower(ResponseStatus)', 'inqueue');
$this->db->where('SMSUsageID', $smsUsageID);
return $this->db->count_all_results('smslog_tbl');
}
but this function generates a query like :
SELECT COUNT(*) AS `numrows` FROM `smslog_tbl` WHERE lower(ResponseStatus) = 'submitted'OR lower(ResponseStatus) = 'inqueue' AND `SMSUsageID` = '1'
these two queries generates different results because of the conditions. I need to group OR conditions to get the correct result. How can i do this with codeigniter
function submittedSMSCount($smsUsageID) {
$this->db->where(
"(lower(ResponseStatus) = 'submitted' OR lower(ResponseStatus) = 'inqueue')",
NULL,
FALSE
);
$this->db->where('SMSUsageID', $smsUsageID);
return $this->db->count_all_results('smslog_tbl');
}
$this->db->where()
accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.
Ref : https://www.codeigniter.com/userguide3/database/query_builder.html#looking-for-specific-data
function submittedSMSCount($smsUsageID) {
$where = "(lower(ResponseStatus) = 'submitted' OR lower(ResponseStatus) = 'inqueue')";
$this->db->where($where);
$this->db->where('SMSUsageID', $smsUsageID);
return $this->db->count_all_results('smslog_tbl');
}
use group with query builder
function submittedSMSCount($smsUsageID) {
$this->db->select('COUNT(*) AS numrows')
->group_start()
->where('lower(ResponseStatus)', 'submitted')
->or_where('lower(ResponseStatus)','inqueue')
->group_end()
->where('SMSUsageID', $smsUsageID);
return $this->db->get('smslog_tbl');
}
output
SELECT COUNT(*) AS numrows FROM smslog_tbl
WHERE ( lower(ResponseStatus) = 'submitted' OR lower(ResponseStatus) = 'inqueue' ) AND SMSUsageID
= '1'