[FIXED] A Database Error Occurred Error Number: 1055

Issue

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

Solution

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

Answered By - Vijay Sharma