2. PHPRunner General questions
  3. PHP error when using PostgreSQL's date fields in quick search pane
This topic is locked

PHP error when using PostgreSQL's date fields in quick search pane

 		6/18/2010 1:53:32 PM
PHPRunner General questions
D
 drgregory author

Dear sirs at this forumn,
I have a project built on a PostgreSQL database. When I am in any list page and I try to make a quick search over a date or numeric field using the options "Contains" or "Start with", no matter the input value written in the search box, I get a PHP error. This error is caused because the internal SQL sentences builder uses the LIKE '%<text>%' in the WHERE clause, and the LIKE is not applicable to date or numeric fields. Is there a way to disable these "Contains" or "Start with" when a date or numeric field is selected, or any kind of solution to this issue?
PD: Of course I know that in the Advanced Search page this problem doesn´t exist, because each table field has its own applicable set of options. But it's really important for me to solve the issue in the quick search panel of the list pages.
Thank you in advance,
DrGregory

Buenos Aires
J
 		Jane 6/23/2010

Hi,
thank you for pointing me to this bug.

We'll fix it in the next update.
To fix this issue in your files open PHPRunner5.2/source/commonfunctions.php file, find this code:

if(IsCharType($type) && !$btexttype)

return $gstrField." ".$like." ".isEnableUpper("'%".$SearchFor."%'");

else

return $gstrField." ".$like." '%".$SearchFor."%'";



and replace it with this one:

if(IsCharType($type) && !$btexttype)

return $gstrField." ".$like." ".isEnableUpper("'%".$SearchFor."%'");

else if(!IsDateFieldType($type))

return $gstrField." ".$like." '%".$SearchFor."%'";

else

return "";



Then find this code:

if(IsCharType($type) && !$btexttype)

return $strField." ".$like." ".isEnableUpper("'%".$sSearchFor."%'");

else

return $strField." ".$like." '%".$sSearchFor."%'";



and replace it with this one:

if(IsCharType($type) && !$btexttype)

return $strField." ".$like." ".isEnableUpper("'%".$sSearchFor."%'");

else if(!IsDateFieldType($type))

return $strField." ".$like." '%".$sSearchFor."%'";

else

return "";


Make the same changes for 'Start with ...' option in the StrWhereExpression and StrWhereAdv functions.
Then open and rebuild project.
Let me know how it works.
D
 		drgregory author 6/23/2010

Dear Jane,
I tried your code and the result is that when applying the "Contains" and "Start with" options to a date field (allways in the quick search), the PHP error message page doesn't appear any more, as if it was hidden from now on. Instead of this, the List page shows the hole set of records, ignoring any of the filters I tried (for example: <date field> Contains 2009 or <date field> Start with 10-05-).
Is this what you intended to fix by the moment? In other words, is the real solution to this problem more complex and you'll put it in the next version of PHPRunner?
I'll keep waiting for your answer. By the way, where are you from?
Best regards,
DrGregory

Buenos Aires
J
 		Jane 6/24/2010

Hi,
it's a temporary fix to ignore these fields in the search. We'll fix this bug on the next version.

As workaround you can convert dates to string values in the SQL query on the Edit SQL querytab and then use these string values for the search.
I'm from Russia.