1

When I try to run this query in CodeIgniter 3, it gives me this error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'forum.phrases.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT `id`, `keyword`, `value`, `language` FROM `phrases` GROUP BY `language`

PHP:

$query = $this->db->select("id, keyword, value, language") ->group_by("language") ->get("phrases") ->result();

I have googled a bit but I don't quite understand the answers, mainly because the queries are not related to CI and are very complicated... How can this be fixed in codeigniter?

I do not wish to change any MySQL settings.

Improve this question
2
  • Have you tried with $query = $this->db->query("SELECT id, keyword, value, language FROM phrases GROUP BY language"); var_dump($query->result());? Commented Feb 26, 2018 at 16:59
  • We don't have a minimal reproducible example here. We don't know which value you wish to present in each grouped row (and neither does MySQL). If you only want DISTINCT languages, tell us. If you want to create comma-separated strings from grouped values, tell us. Commented Oct 20 at 23:13

2 Answers 2

Reset to default
5

Expression #1

SELECT list is not in GROUP BY clause and contains nonaggregated column 'spd.quantity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Just add the following line at the above of your query.

$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.
1

This is not a CodeIgniter specific problem -- it is part of the SQL standard and is built into MySQL. It exists so that it can validate your data and encourage good database design and queries. You have two options to fix it:

  1. You can write queries the "correct" way. This was an issue with the SQL92 specification, but was later modified in the SQL99 standard to permit non-aggregates: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

  2. The other way, and more common response, is to modify your my.cnf file and disable the forced SQL mode of only_full_group_by. Disable ONLY_FULL_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.