nuBuilder Forum

Posted: **Tue Apr 25, 2023 5:01 pm**

Hi,
I am adding users to my nuBuilder site.
I can add users, and assign them an access level, and they are able to login, but only one user is shown when I browse "Users".
THis is the error found in Debug:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1625603_1.zzzzsys_access.sal_code' which is not in SELECT list; this is incompatible with DISTINCT

===SQL===========

 SELECT DISTINCT CONCAT(sal_code,' : ',sal_description) FROM `zzzzsys_user`
            INNER JOIN zzzzsys_access ON zzzzsys_access_id = sus_zzzzsys_access_id ORDER BY sal_code 

===BACK TRACE====

/web/htdocs/---hidden---/core/nucommon.php(1406) : eval()'d code - line 31 (nuRunQuery)

/web/htdocs/---hidden---/core/nucommon.php(1406) : eval()'d code - line 44 ({closure})

/web/htdocs/---hidden---/core/nucommon.php - line 1406 (eval)

/web/htdocs/---hidden---/core/nuform.php - line 21 (nuEval)

/web/htdocs/---hidden---/core/nuform.php - line 1133 (nuBeforeBrowse)

/web/htdocs/---hidden---/core/nuform.php - line 460 (nuBrowseColumns)

/web/htdocs/---hidden---/core/nuapi.php - line 84 (nuGetFormObject)

Thank you

Posted: **Wed Apr 26, 2023 6:56 am**

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

Posted: **Wed Apr 26, 2023 12:43 pm**

Solved. Thank you, also for the explanation. I am not a MySQL geek, so I appreciate it

Posted: **Thu Apr 27, 2023 4:14 pm**

You're welcome!

nuBuilder Forum

Bug in adding users?

Bug in adding users?

Re: Bug in adding users?

Re: Bug in adding users?

Re: Bug in adding users?