database error when using DISTINCT and ORDER BY |
hello everyone
i am new to code igniter and got stuck in one query i want to get distinct id from database order it by id my code is PHP Code: $this->db->select('message_user_id, message_user_name'); i get this error when i run it i am using wamp with php 7 and mysql 5.7 Code: Error Number: 3065 how can i fix it please help me
Try this:
Code: $this->db->select('message_user_id, message_user_name, message_id');
This select thing is working if i dont use order by but it shows error when order by is used with distinct or group by this error only shows when mysql is 5.7 they changed some stuff in it and i think codeigniter $this->distinct is not fixed for this type query in latest mysql version
ok i need to repeat my error in details
i want to get only one user_id from my inbox db by using group by or distinct but both not working here is my sql code for group by Code: SELECT message_user_name FROM cms_inbox GROUP BY message_user_id ORDER BY message_time DESC and error i get from it in only latest version on mysql 5.7 is Code: Error Number: 1055 demo on w3schools website HERE it works on that website but dont know why my code is showing error
my problem solved by using
Code: SELECT ANY_VALUE(message_user_name), ANY_VALUE(message_user_id) FROM cms_inbox GROUP BY message_user_id ORDER BY ANY_VALUE(message_time) adding ANY_VALUE() to SELECT and ORDER BY solved my problem but causing problem in view file this is showing when print_r uses Code: [color=#333333][size=medium][font=Ubuntu, sans-serif]Array ( [0] => Array ( [ANY_VALUE(message_user_name)] => user_1 [ANY_VALUE(message_user_id)] => 2 ) [1] => Array ( [ANY_VALUE(message_user_name)] => user_2 [ANY_VALUE(message_user_id)] => ) ) and when i use foreach is view file Code: foreach ( $inbox as $var) { echo $var->message_user_name; } it shows error Code: [color=#333333][size=medium][font=Ubuntu, sans-serif]Message: Trying to get property of non-object[/font][/size][/color]
Does this not work?
Code: $this->db->select('distinct message_user_id, message_user_name, message_id');
guyz seems like you dont understand problem is with mysql version 5.7 needs change in code or change mysql 5.7 mode
by default in 5.7 one mode is set to ONLY_FULL_GROUP_BY either disable it or go back to old 5.6 mysql i fixed this problem by disabling only full group by now here is what is happening and how it can be fixed Code: SELECT message_user_name FROM cms_inbox GROUP BY message_user_id ORDER BY message_time DESC this code shows error in mysql 5.7 Code: Error Number: 1055 how to fix it simply go back to mysql 5.6 or disable ONLY_FULL_GROUP_BY mode seems like no one use group by regularly in codeigniter thats why no fixed querying in codeigniter which cause error in latest version of mysql 5.7 while works with every db before it thank you guys my problem solved by disable ONLY_FULL_GROUP_BY but hoping for future proof codeigniter team fixes this query by making it compatible with mysql 5.7 or giving lil info how to setup query to use it with newer mysql here is more info about this mode mysql 5.7
Try this:
Code: SELECT ANY_VALUE(message_user_name) message_user_name, ANY_VALUE(message_user_id) AS message_user_id A good decision is based on knowledge and not on numbers. - Plato
|
Welcome Guest, Not a member yet? Register Sign In |