2

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

Improve this question
7
  • 1
    Changed the database from mysql to mysqli do you mean you upgraded mysql from 5.6 to 5.7? mysqli and mysql_ are different PHP drivers to interact with mysql. Commented Sep 12, 2017 at 11:27
  • You might try matching your ( and ) brackets in the query Commented Sep 12, 2017 at 11:28
  • Remove the *, in both queries and it will work. If you do a group by, you either have to use aggregation functions (like GROUP_CONCAT) or put columns you select into the group by clause. Commented Sep 12, 2017 at 11:30
  • installed phpmyadmin using one click install which runs Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 , phpmyadmin vVersion information: 4.5.4.1deb2ubuntu2ersion . Then imported existing db Commented Sep 12, 2017 at 11:30
  • You'll need to look at what mysql version you are running, 5.6 and 5.7 handle group bys differently. Commented Sep 12, 2017 at 11:33

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

did you follow steps
using which OS?
@VijaySharma That page is a 404. Best to use the primary source, dev.mysql.com/doc/refman/5.7/en/group-by-handling.html. I also wouldn't change the mode, better to correct the query.
@chris85 i changed query but its not working for me so i used that it work fine for me , Ya i know that better to correct query rather then changing mode , thanks for your suggestion .
@vijaySharma, your solutions seems to be a perfect answer, i dont want to rewrite all the queries in the app, thats why
|
1

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',''));
Improve this answer

1 Comment

That does the trick for me with laragon mysql 5.7
0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.