I am generating a report in PHP Runner and it generates an sql error when using advanced search.
The report is generated with the following sql:
SELECT
`_users`.displayname,
`_task_list`.`start`,
TIMEDIFF(`_task_list`.`end`, `_task_list`.`start`) AS elapsed,
`_task_list`.`count`,
`_task_list`.comments,
`_task_list`.source,
`_process`.processname
FROM `_task_list`
INNER JOIN `_users` ON `_task_list`.username = `_users`.username
INNER JOIN `_process` ON `_task_list`.process = `_process`.processID
The report generates fine. When I try to use advanced search on the report php runner generates an sql error.
Error type 256
Error description You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((1=1 and upper(`_users`.displayname) like upper('%Basford, Mary%')) ) AND (1=1 ' at line 1
URL 10.100.77.18/development/ap/InnoTraxRS/Task_Count_report.php?
Error file C:\Innosource\development\AP\InnoTraxRS\include\dbconnection.php
Error line 34
SQL query SELECT original.*, `displayname` as `grp0`, DATE(`start`) as `grp1`, `start` as `grp1MIN` FROM (SELECT `_users`.displayname, `_task_list`.`start`, TIMEDIFF(`_task_list`.`end`, `_task_list`.`start`) AS elapsed, `_task_list`.`count`, `_task_list`.comments, `_task_list`.source, `_process`.processname FROM `_task_list` INNER JOIN `_users` ON `_task_list`.username = `_users`.username INNER JOIN `_process` ON `_task_list`.process = `_process`.processID WHERE (1=1 and upper(`_users`.displayname) like upper('%Basford, Mary%')) ((1=1 and upper(`_users`.displayname) like upper('%Basford, Mary%')) ) AND (1=1 and upper(`_users`.displayname) like upper('%Basford, Mary%')) ) original WHERE ((`displayname` >= 'Basford, Mary' AND `displayname` <= 'Basford, Mary')) ORDER BY `displayname` ASC, DATE(`start`) ASC
Solution
This is a general error. It occurs when there is an error in event code or in SQL.
This has been a consistent problem with reports.
What can I do to resolve this issue.
KJ Plunkert