[FX.php List] Need Help With A Complex Query, This AND That OR This AND That...

[Derek Bastille]

Rich,
   What about writing a script in the database that would do the  
logic for you?  That is:

-- you pass a comma delimited list of IDs to FM
  - this will probably require a separate layout and a global  
SearchIDs field to plop the list in to
-- Ask FM to run a script that does
  - breaks the list into separate IDs
  - builds a query with a separate req for each ID (with the active  
and dupe checks)
  - sorts the found set by JobNumber

   The limitation of this, of course, is that if you wanted to find  
all IDs that are active (but that may or may not be dupes) you will  
need a new script.  Would be cool if CWP took script params, but I  
don't think it does.

Regards,
   Derek

On Nov 14, 2005, at 10:42 AM, Chris Hansen wrote:

> Rich,
>
> What you're running into is a limitation in the way that FileMaker  
> built their CWP interface.  Basically, they didn't want the various  
> parameters to have to be entered in a specific value, so they  
> limited queries to either 'OR' or 'AND' queries.  Grrr.
>
> What this means is that in cases where I have to use both logical  
> operators for a single list, I perform multiple FileMaker queries  
> which are as specific as possible, combine the results using PHP,  
> and then perform any needed sorts.  If someone else has a better  
> way of doing this, let me know.  Of course, if you have ODBC set  
> up, keep in mind that the latest version of FX.php supports ODBC  
> with FileMaker, so you could just perform your query via ODBC =)
>
> HTH  I'm short on time these days, so I won't write more now, but  
> let me know if you need additional details.  Best,
>
> --Chris Hansen
>   creator of FX.php
>   "The best way from FileMaker to the Web."
>   www.iViking.org
>
> On Nov 14, 2005, at 11:27 AM, Rich Aber wrote:
>
>> Hello, I'm very new to FX.php, and need some help figuring out how  
>> to code a query.
>> I'm searching an FM database for records relating to Customers,
>> based on the Customer's ID, and only returning records that are  
>> 'Active',
>> and omitting records that have a flag for 'Duplicate',
>> then sorting by a parameter such as 'JobNumber.'
>>
>> This code accomplishes that task:
>> $searchName->AddDBParam('ID','1234');
>> $searchName->AddDBParam('Active','Active');
>> $searchName->AddDBParam('Dupes','x',$op="neq");
>> $searchName->AddSortParam('JobNumber','ascend');
>>
>> However, I need to expand upon this, because we have some  
>> customers with multiple IDs.
>> I thought something like this might work:
>> $searchName->AddDBParam('ID','1234');
>> $searchName->AddDBParam('ID','5678');
>> $searchName->AddDBParam('Active','Active');
>> $searchName->AddDBParam('Dupes','x',$op="neq");
>> $searchName->AddSortParam('JobNumber','ascend');
>>
>> But that code returns no results.
>>
>> I then tried using the -lop operator, like this:
>> $searchName->AddDBParam('ID','1234');
>> $searchName->AddDBParam('-lop', 'or'); //Does this make all of the  
>> DBParams OR?!?!
>> $searchName->AddDBParam('ID','5678');
>> $searchName->AddDBParam('Active','Active');
>> $searchName->AddDBParam('Dupes','x',$op="neq");
>> $searchName->AddSortParam('JobNumber','ascend');
>>
>> That code returns EVERYTHING in my database, regardless of 'CustID.'
>>
>> How do I get the 'CustID' parameter to be logical OR,
>> and the other parameters to be logical AND?
>>
>> I know in MySQL I could write a statement similar to:
>> SELECT ID, Active, Dupes, JobNumber FROM Jobs WHERE ID='1234' OR  
>> ID='5678' AND Active='Active' AND Dupes!='x';
>> ...or something to that effect, but how would I do this with FX.php?
>>
>> Any help would be greatly appreciated.
>>
>> Thank you,
>>
>> Richard Aber
>>
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>
>>
>
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list