Changed the database from MySQL to MySQLI and getting the error - A Database Error Occurred
Error Number: 1055
Expression #23 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zipbizzlatestdb.pia.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants
FROM `pr_opportunity` as o
LEFT OUTER JOIN pr_internal_applicant as pia
ON o.id = pia.positionid
WHERE o.approval_status='Approved'
and DATE(o.deadline) > DATE(NOW())
GROUP BY o.id
Filename: /var/www/html/singlecodebase/Feb152017/models/mod_common.php
Line Number: 6999
My model file mod_common is as below:
function get_opportunity_list()
{
$sql = "SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants
FROM `".$this->myTables['opportunity']."` as o
LEFT OUTER JOIN pr_internal_applicant as pia
ON o.id = pia.positionid
WHERE o.approval_status='Approved'
and DATE(o.deadline) > DATE(NOW())
GROUP BY o.id";
$query=$this->db->query($sql);
if($query->num_rows() > 0){
$rows = $query->result();
}
return $rows;
}
No clue how to solve this error
it occurs in mysql 5.7 so check this link [https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/] and follow steps it work fine for me.
Or open file
vi /etc/mysql/my.cnf
Add these line at the bottom of the file
[mysqld]
# ... other stuff will probably be here
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
and save then restart mysql
sudo service mysql restart
Note not for window os