zzzzsys_form.nuDebug and the browser developer console for errors.nuDebug($sql); before the line nuCreateTableFromSelect('#TABLE_ID#', $sql);.All present
nuDebug($sql); before the line nuCreateTableFromSelect('#TABLE_ID#', $sql);.
nuBuilder Forte 4.74. Which version did you upgrade from?
SELECT DISTINCT … ORDER BY X where X isn’t in the SELECT list: in newer MySQL versions (5.7+, 8.0+) with ONLY_FULL_GROUP_BY enabled, this is disallowed because ordering by a column not included in the result set alongside DISTINCT can yield unpredictable results, so the server throws error 3065—older versions or installations with that SQL mode disabled may not show it.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_demo`, `sph_template`, `sph_hide`, `sph_json`) VALUES ('nuform_BB', 'nuform_BB', 'System PHP', 'nubuilder', '// Temp table\r\n\r\n$denied = $GLOBALS[\'nuSetup\']->set_denied == 1;\r\n\r\n$condition = $denied\r\n ? \"(zzzzsys_form_id NOT LIKE \'nu%\' OR zzzzsys_form_id = \'nuuserhome\')\"\r\n : \"1=1\";\r\n\r\n$sql = \"\r\n SELECT *\r\n FROM zzzzsys_form\r\n WHERE $condition\r\n ORDER BY sfo_code\r\n\";\r\n\r\nnuCreateTableFromSelect(\'#TABLE_ID#\', $sql);\r\n\r\n\r\n// Group Filter\r\n\r\n\r\n$getDistinctGroupColumnQuery = function($column, $condition) {\r\n return \"\r\n SELECT \r\n DISTINCT TRIM(`$column`) AS trimmed_group\r\n FROM \r\n zzzzsys_form\r\n WHERE \r\n $condition AND (\r\n IFNULL(`$column`, \'\') <> \'\') \r\n ORDER BY \r\n trimmed_group\r\n \";\r\n};\r\n\r\n$sqlGroup = function($condition) use ($getDistinctGroupColumnQuery) {\r\n return $getDistinctGroupColumnQuery(\'sfo_group\', $condition);\r\n};\r\n\r\n$group = nuEncodeQueryRowResults($sqlGroup($condition), [], [\'\']);\r\n\r\n$filterJS = \"\r\n function getData(data) {\r\n return JSON.parse(atob(data));\r\n }\r\n function getGroup() {\r\n return getData(\'$group\');\r\n }\r\n\";\r\n\r\nnuAddJavaScript($filterJS);\r\n', '', '', '1', '0', '1', '0', '', NULL)
thanks a lot, kev1n! Everything is working fine now!kev1n wrote: ↑Sun Jun 08, 2025 10:41 am This error appears when usingSELECT DISTINCT … ORDER BY Xwhere X isn’t in the SELECT list: in newer MySQL versions (5.7+, 8.0+) with ONLY_FULL_GROUP_BY enabled, this is disallowed because ordering by a column not included in the result set alongside DISTINCT can yield unpredictable results, so the server throws error 3065—older versions or installations with that SQL mode disabled may not show it.
Try this fix:
Code: Select all
# mia sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Code: Select all
SELECT itm_value, itm_description
FROM zzzzsys_item
WHERE itm_object_id = '#OBJECT_ID#' AND itm_active = '1'
ORDER BY itm_descriptionError:kev1n wrote: ↑Mon Jun 09, 2025 10:30 am Hmm, the SQL under Objects → Input → InputType looks like this, which could be causing the issue:
Could you share the exact error message you're seeing?Code: Select all
SELECT itm_value, itm_description FROM zzzzsys_item WHERE itm_object_id = '#OBJECT_ID#' AND itm_active = '1' ORDER BY itm_description
Code: Select all
UPDATE `zzzzsys_php` SET `sph_php` = '// Temp table\n\n$denied = $GLOBALS[\'nuSetup\']->set_denied == 1;\n\n$condition = $denied\n ? \"(zzzzsys_form_id NOT LIKE \'nu%\' OR zzzzsys_form_id = \'nuuserhome\')\"\n : \"1=1\";\n\n$sql = \"\n SELECT *\n FROM zzzzsys_form\n WHERE $condition\n ORDER BY sfo_code\n\";\n\nnuCreateTableFromSelect(\'#TABLE_ID#\', $sql);\n\n\n// Group Filter\n\n\n$getDistinctGroupColumnQuery = function($column, $condition) {\n return \"\n SELECT \n DISTINCT TRIM(`$column`) AS trimmed_group\n FROM \n zzzzsys_form\n WHERE \n $condition AND (\n IFNULL(`$column`, \'\') <> \'\') \n ORDER BY \n trimmed_group\n \";\n};\n\n$sqlGroup = function($condition) use ($getDistinctGroupColumnQuery) {\n return $getDistinctGroupColumnQuery(\'sfo_group\', $condition);\n};\n\n$group = nuEncodeQueryRowResults($sqlGroup($condition), [], [\'\']);\n\n$filterJS = \"\n function getData(data) {\n return JSON.parse(atob(data));\n }\n function getGroup() {\n return getData(\'$group\');\n }\n\";\n\nnuAddJavaScript($filterJS);' WHERE `zzzzsys_php`.`zzzzsys_php_id` = 'nuform_BB';I tried it. There is no effect. I will continue to work with MySQL version 5.7.kev1n wrote: ↑Tue Jun 10, 2025 3:14 am Run this update query:Code: Select all
UPDATE `zzzzsys_php` SET `sph_php` = '// Temp table\n\n$denied = $GLOBALS[\'nuSetup\']->set_denied == 1;\n\n$condition = $denied\n ? \"(zzzzsys_form_id NOT LIKE \'nu%\' OR zzzzsys_form_id = \'nuuserhome\')\"\n : \"1=1\";\n\n$sql = \"\n SELECT *\n FROM zzzzsys_form\n WHERE $condition\n ORDER BY sfo_code\n\";\n\nnuCreateTableFromSelect(\'#TABLE_ID#\', $sql);\n\n\n// Group Filter\n\n\n$getDistinctGroupColumnQuery = function($column, $condition) {\n return \"\n SELECT \n DISTINCT TRIM(`$column`) AS trimmed_group\n FROM \n zzzzsys_form\n WHERE \n $condition AND (\n IFNULL(`$column`, \'\') <> \'\') \n ORDER BY \n trimmed_group\n \";\n};\n\n$sqlGroup = function($condition) use ($getDistinctGroupColumnQuery) {\n return $getDistinctGroupColumnQuery(\'sfo_group\', $condition);\n};\n\n$group = nuEncodeQueryRowResults($sqlGroup($condition), [], [\'\']);\n\n$filterJS = \"\n function getData(data) {\n return JSON.parse(atob(data));\n }\n function getGroup() {\n return getData(\'$group\');\n }\n\";\n\nnuAddJavaScript($filterJS);' WHERE `zzzzsys_php`.`zzzzsys_php_id` = 'nuform_BB';