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

[Chris Hansen]

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
>
>