How can FX.php query multiple related tables?

fx.php

April 26, 200619 yr

I am running FM7SA, PHP 5.1.2, Win 2003 and FX.php v4.2

I am using FX.php to search a layout that has fields from multiple related tables.


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

$Search->SetDBData('FMFile.fp7','LayoutTableA');

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



$Search->AddDBParam('Field1','$post-value1');

$Search->AddDBParam('Field2',$post-value2);

$Search->AddDBParam('TableB::Field9',$post-value3);



$SearchResult=$Search->FMFind();

$FoundCount =  $SearchResult['foundCount'];



foreach($SearchResult['data'] as $SearchKey=>$SearchRecord) { 

...

}

When it Works

I can perform the query directly in the FM layout just fine. It only pukes via PHP.

If I delete the related field from the layout and remove

$Search->AddDBParam('TableB::Field9',$post-value3);

they query executes just fine (of course, I'm only searching one table).

I am able to query multiple tables just fine in instances where the related field is not null. In addition, this query DOES work if the result finds values in the related field. It breaks if the related field is left empty (i.e. if all fields are left empty and the user just clicks "Find", a common scenario)

When it Doesn't Work

However, if the result has any null values in the related field, I get fatal PHP errors: "Fatal error: Cannot use object of type FX_Error as array" for any line that returns the $SearchResult. (In PHP 4.3.9, the error was "Cannot use a scalar value as an array" on the foreach statement)

This one really has me stumped. I suspect PHP cannot properly build the result array because nulls are present.

Your Thoughts?

Do you have any ideas how I might trap for nulls, but still display results? I'd like the logic to be in PHP, but at this point, I'll consider modifying the FM database or layouts.

Or is there a better way to query multiple tables with FX.php?

Thank you very much in advance!!!

Shannon

-=-=-

2 weeks later...

May 8, 200619 yr

Author

Okay gang, I fixed this. Rereading my OP, my description is a bit confusing. Let's see if I can befuddle you further.

Here's the dealio on querying related tables on a single page:

1) In FileMaker, I have Table A and Table B. In order to allow a many-to-many relationship, I have a third table, Table AB.

2) I am searching LayoutTableA.

3) My code above is fine (although TableB should be TableAB).

What does not work:

In FileMaker, I added a related field to LayoutTableA: Table AB::Field9. In FX.php, I used the above code to query that field and I got strange errors that led me to upgrade PHP, test for nulls and a couple other goose chases.

What does work:

In FileMaker, I added a related field to LayoutTableA via a portal Table AB::Field9. In FX.php, I used my code to query that field and it worked perfectly.