A Database Error Occurred Error Number: 1055 [duplicate]

Question

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

Answer 1

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

Answer 2

This worked for me:

Open http://localhost/phpmyadmin/ then click SQL and run this query :

1. SET sql_mode = '';
2. SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Answer 3

In reading your SQL, you are asking to return every column (i.e. *), as well as the id a second time (o.id is already retrieved by *), then you are querying for the aggregate function of GROUP_CONCAT(pia.applicantid). However, your group by Clause only requests to aggregate by (group by) o.id.

SQL doesn't like this because it doesn't know what to do with the other fields in your dataset that you are querying for using *.

I suspect you want to see every applicantid related to o.id in an aggregate fashion. If so, this is your query and it will work.

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"

If you want to group by other fields, then just add them specifically in your select as well as your group by. All non-aggregate fields need to be in both the select and the group by. This is part of the ANSI Standard and should be true for all SQL flavors. I believe mysql 5.7 and up is more on this than the prior versions, but I'd follow this as a rule.

Here's some supplemental reading regarding the topic. Hope this helps. MySQL 5.7 Documentation regarding Group by