2. PHPRunner General questions
  3. [SOLVED]  Bug? Problem with modiying $strSQLWhere on advanced
This topic is locked
[SOLVED]

 Bug? Problem with modiying $strSQLWhere on advanced

 		4/26/2011 1:28:27 AM
PHPRunner General questions
G
 giles author

Hi,

Think I've uncovered a bug...
Application has a list page with a BeforeSQL event that modifies the $strSQLWhere.
All works fine until performing an advanced search. This results in a SQL Query error in the gSQLRowCount_int function in the commonfunctions.
The SQL query shows that the "AND" is not being inserted between the advanced search section and the modified $strSQLWhere section of the query.
That is, query is:

select count() FROM mytable where ((1=1) and (upper(mytable.Field1) like upper('%mytext%'))(mytable.Field2 is true)
instead of:

select count() FROM mytable where ((1=1) and (upper(mytable.Field1) like upper('%mytext%')) AND (mytable.Field2 is true)
Is there a work-around for this?
Sergey Kornilov admin 4/26/2011

Show us your BeforeSQLQuery event code.
PS. what version of PHPRunner do you run?
G
 		giles author 4/26/2011



Show us your BeforeSQLQuery event code.
PS. what version of PHPRunner do you run?


Hi Admin,

I running PHPRunner 5.3 Build 7474.
The list page concerned has "Show search panel" and "AJAX search, pagination and sorting" options selected. In search setting the Search Panel has no fields selected. Various fields are selected in the Searchable, Advanced search and 'All fields' search. Add/edit/view are popups.
The basic query for the page is in a custom view and involves a join

SELECT

contacts.ID,

...(many fields)....

statuscodes.Registered

FROM contacts

LEFT OUTER JOIN statuscodes ON contacts.Status = statuscodes.Code

WHERE (contacts.FIRST is not null OR contacts.LAST is not null) AND (contacts.PHONE is not null OR contacts.MOBILE is not null)

ORDER BY contacts.DateLastUse
The BeforeSQLQuery event code is:

$strWhereClause .= '((statuscodes.PleaseContact =-1 AND statuscodes.CallSoon =0) OR statuscodes.PleaseContact is null)';

$strWhereClause .= ' AND (contacts.AssignedID is null or contacts.AssignedID='.$_SESSION["usersIDnumber"].')';
Works fine except when searching. The Advanced Search gives the SQL error I described. I've also found that searches with the panel result in a JSON syntax error.
Hope this helps.
Sergey Kornilov admin 4/26/2011

There is an error in your code - you don't add AND to existing WHERE clause.
I recommend to use whereAdd() function that takes care of this.

More info: http://xlinesoft.com/phprunner/docs/whereadd.htm
G
 		giles author 4/26/2011

Excellent.
Just to wrap up for any other users, that code becomes:
$mywhere = '((statuscodes.PleaseContact =-1 AND statuscodes.CallSoon =0) OR statuscodes.PleaseContact is null)';

$mywhere .= ' AND (contacts.AssignedID is null or contacts.AssignedID='.$_SESSION["usersIDnumber"].')';
$strWhereClause = whereAdd($strWhereClause, $mywhere);
Thanks for your assistance.