How to disable ONLY_FULL_GROUP_BY in codeigniter?

I have worked may time with Group BY in mysql but recently i have received a problem of Group By. I searched from many sites and I got the problem that its due to upgrading the mysql version in which it is enabled and in old versions it was disabled.

I won’t remove it using the query but from the code. I also don’t want to remove from my.cnf because when the site will live it will occure again.

so please suggest me a solution that works in codeigniter atleast. I will be thankful to you 🙂

I have tried below query :

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

it worked but when the server restarts the problem occurs again.

Answer

Codeigniter has stricton option in application/config/database.php:

$db['default'] = array(
    ...
    'stricton' => FALSE, // forces 'Strict Mode' connections
    ...
);

Or you can disable sql_mode params like this:

$this->db->query('SET SESSION sql_mode = ""');

// ONLY_FULL_GROUP_BY
$this->db->query('SET SESSION sql_mode =
                  REPLACE(REPLACE(REPLACE(
                  @@sql_mode,
                  "ONLY_FULL_GROUP_BY,", ""),
                  ",ONLY_FULL_GROUP_BY", ""),
                  "ONLY_FULL_GROUP_BY", "")');