There is a setting/configuration that affects the behavior of the ORDER BY clause when used with the DISTINCT keyword in MySQL.
By default, MySQL requires that all columns in the ORDER BY clause must be present in the SELECT list when the DISTINCT keyword is used. This is because the DISTINCT keyword removes duplicates from the result set, and the database needs to know which column(s) to use for sorting the distinct values.
To rectify the PHP code for the user form, executing the following SQL query (e.g. in phpMyAdmin)
Code: Select all
REPLACE INTO `zzzzsys_php` (`zzzzsys_php_id`, `sph_code`, `sph_description`, `sph_group`, `sph_php`, `sph_run`, `sph_zzzzsys_form_id`, `sph_system`, `sph_global`, `sph_hide`, `sph_json`) VALUES ('nuuser_BB', 'nuuser_BB', 'System PHP', 'nubuilder', '$getDistinctUserColumnQuery = function($column) {\r\n return \"SELECT DISTINCT `$column` FROM `zzzzsys_user` WHERE IFNULL(`$column`,\'\') <> \'\' ORDER BY `$column`\";\r\n};\r\n\r\n$sqlPosition = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_position\');\r\n};\r\n\r\n$sqlTeam = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_team\');\r\n};\r\n\r\n$sqlDepartment = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_department\');\r\n};\r\n\r\n$sqlLanguage = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_language\');\r\n};\r\n\r\n$sqlAccessLevel = function() {\r\n $sql = \" SELECT DISTINCT CONCAT(sal_code,\' : \',sal_description) AS acl FROM `zzzzsys_user`\r\n INNER JOIN zzzzsys_access ON zzzzsys_access_id = sus_zzzzsys_access_id ORDER BY acl \";\r\n return $sql;\r\n};\r\n\r\n\r\n$position = nuEncodeQueryRowResults($sqlPosition(), [], [\'\']);\r\n$team = nuEncodeQueryRowResults($sqlTeam(), [], [\'\']);\r\n$department = nuEncodeQueryRowResults($sqlDepartment(), [], [\'\']);\r\n$language = nuEncodeQueryRowResults($sqlLanguage(), [], [\'\']);\r\n$accessLevel = nuEncodeQueryRowResults($sqlAccessLevel(), [], [\'\']);\r\n\r\n$filterJS = \"\r\n function getData(data) {\r\n return JSON.parse(atob(data));\r\n }\r\n function getPosition() {\r\n return getData(\'$position\');\r\n }\r\n function getTeam() {\r\n return getData(\'$team\');\r\n }\r\n\r\n function getDepartment() {\r\n return getData(\'$department\');\r\n }\r\n function getLanguage() {\r\n return getData(\'$language\');\r\n }\r\n\r\n function getAccessLevel() {\r\n return getData(\'$accessLevel\');\r\n }\r\n\";\r\n\r\n\r\n$addCode = $_SESSION[\'nubuilder_session_data\'][\'USER_CODE_LABEL\'] ?? \'\';\r\n\r\n$addCodeJS = \"\r\n\r\n if (\'$addCode\' !== \'\') {\r\n $(\'#nusort_5\').html(\'$addCode\')\r\n };\r\n\r\n\";\r\n\r\n\r\nnuAddJavaScript($filterJS . $addCodeJS);', NULL, NULL, '1', '0', NULL, NULL)
This should fix the issue.
However, MySQL also provides a configuration option called sql_mode, which can be set to a value that allows the use of an ORDER BY clause on a column that is not in the SELECT list when DISTINCT is used. This configuration option can be set globally, for a specific session, or even for a specific query.
For example, you could set the sql_mode to include the "ONLY_FULL_GROUP_BY" value to enforce the default behavior or to "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" to allow the non-standard behavior.
To set the sql_mode globally, you can modify the MySQL configuration file, usually located at /etc/mysql/my.cnf or /etc/my.cnf. In this file, add the following line under the [mysqld] section:
Code: Select all
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION