$op-neq problem- multi predicate search.

[Kranky]

Code:

$query->AddDBParam('customer',"cust7","eq");

$query->AddDBParam('status',"closed","neq");

this search should give me all the items for customer - cust7 whose item status is not closed(ie, open).

this in filemaker terms would be doing two find request. one for customer = cust7 and another for status = closed ( with the omit tick box checked) so it would give me a result set of 3 which is correct.

the fx.php search however gives me 57, obviously incorrect.

i even tried with the 'lop' set to 'and' without any joy.

if anyone has figured it out, give us a hint.

cheers

[epicrecipe]

Can anyone help with this?

I am also trying to figure out how to accomplish a multiple request FM7/FMS7A find via FX.php without nesting loops.

Thanks!

[andygaunt]

You should be able to do this without the -lop.

The following code returns the correct result of 4 from a test on our database.

<?php

include_once('FX/server_data.php');

include_once('FX/FX.php');



$layout = '########';



$query = new FX ($serverIP, $webCompanionPort);

$query -> SetDBData($webDB,$layout);

$query -> SetDBPassword($webPW,$webUN);

$query ->AddDBParam('FirstField','Yes',"neq");

$query -> AddDBParam('SecondField','Yes',"eq");



$queryResult = $query -> FMFind();



echo $queryResult['foundCount'];



?>

If we add in the -lop the result becomes false and returns an incorrect number of records, namely 8.

Can you give us more info on the query you are trying to perform.

[epicrecipe]

I am wanting to find records where:

FirstField = Not empty AND

SecondField = Not 'Closed'

I'm looking for a result very similar to the OP. In FileMaker, we'd perform a find using two requests: 1) First Field = "*" and 2) SecondField = "Closed" with the Omit box checked.

This code provides the result I needed:

<?php



include_once('FX/FX.php');

include_once('FX/server_data.php');



$query = new FX ($serverIP, $webCompanionPort);

$query -> SetDBData($webDB,$layout);

$query -> SetDBPassword($webPW,$webUN);

$query -> AddDBParam('FirstField',' * '); // Note the spaces in ' * '

$query -> AddDBParam('SecondField','Closed','neq');

$query -> AddDBParam('-lop','or');



$queryResult = $query -> FMFind();

?>

Now on to my next problem - using FX.php to query multiple values of the same field like FileMaker allows multiple requests in find mode.

Thank you and hope this helps,

Shannon

[andygaunt]

ok,

You want to find an AND here and yet you state the result is correct. On testing it requires you drop the -lop request to determine the correct result.

<?php



include_once('FX/FX.php');

include_once('FX/server_data.php');



$query = new FX ($serverIP, $webCompanionPort);

$query -> SetDBData($webDB,$layout);

$query -> SetDBPassword($webPW,$webUN);

$query -> AddDBParam('FirstField',' * '); // Note the spaces in ' * '

$query -> AddDBParam('SecondField','Closed','neq');



$queryResult = $query -> FMFind();

?>

Remember the default for FX queries is an AND search.

As to using FX to find on the same field for multiple instances, this is when you would use a -lop with 'or'.

Hope this helps

[Garry Claridge]

Does the "status" field contain any values other than {open,closed}?

Garry

[epicrecipe]

Garry - yes, the SecondField ('status') has many values.

Here is my data set: (32 total records)

FirstField: 27 empty, 5 not empty

SecondField: 4 'Closed', 28 something other than closed.

I'm sorry I wasn't clear. In FileMaker, this gives me the desired result:

Request 1: set FirstField = '*'

Request 2: set SecondField = 'closed' and check the omit box

Result: Success: 3 records where FirstField is not empty and SecondField is not 'closed'.

In FX.php, I get the same desired result with:

$query -> AddDBParam('FirstField',' * ');

$query -> AddDBParam('SecondField','Closed','neq');

$query -> AddDBParam('-lop','or');

$queryResult = $query -> FMFind();

Result: Success: 3 records where FirstField is not empty and SecondField is not closed.

I believe this is the answer for the OP, Kranky.

Now, I originally tried Andy's suggestion:

$query -> AddDBParam('FirstField',' * ');

$query -> AddDBParam('SecondField','Closed','neq');

$queryResult = $query -> FMFind();

Result: No Joy: 30 records:

25 records where FirstField is empty and SecondField is not closed AND

3 records where FirstField is not empty and SecondField is not closed AND

2 records where FirstField is empty and SecondField is closed

(The only two records NOT found were FirstField is not empty and SecondField is Closed)

I get the same result in FileMaker when I perform a find on a single request:

Request 1: set FirstField = '*'

Request 1: set SecondField = 'closed'

Request 1: check the omit box

Therefore, setting the "-lop" to "or" allowed me to have multiple find requests where one had the omit box checked.

Clear as mud? I DO appreciate your feedback

Shannon