Need basic help in PHP API

[gigzta]

Hi all,

I am completely a beginner in Filemaker and PHP API.

Now I am working with an unstructured FM database, contain almost 1000 records and 32 fields in each record.

Image below is describe the table, just only 8 records.

I need solutions

1. I want to perform a newFindAllCommand, and to make it faster (event only get 25 records), I'd like the 'find command' only load id, department and first name field. Sometimes I got:

Fatal error: Maximum execution time of 60 seconds exceeded in...

What command to use when I want only select 3 required fields rather than 32 fields ?

In SQL I can do

"Select id, department, `first name` from employee";

2. If I want to get the department name only, just want to list available 'department name' in the table, which result like:

department

----------

accounting

sales

production

How can I do this, while in SQL I can use 'group by' :

"Select * from employee group by department"

Thanks

[Genx]

In short... you can't, not the way you're trying to do it anyway.

You could perform a filemaker script to get you your unique records in the first place if you wanted... Regarding select of only specific fields - your only solution for that is to create a layout with only those fields on it.

What you have to understand is that the XML engine will return the entire layout, with all fields and found records - also it sucks with larger record sets, and the time taken to construct the XML tends to increase exponentially - hence why it needs to be paged.

If you wanted to go ahead with your listing of people in departments example (in an efficient way), first you would have to normalize your structure and move departments to a seperate table. Base your find on the departments table and add a relationship and portal to your employees table. From there you can take advantage of the API's related record retrieval and manipulation commands.

Edited January 8, 2009 by Guest

[Baloo]

To elaborate on what Genx said. The Filemaker API does not provide you with a resource handle to Filmaker's back end. It is simply a series of objects that facilitate the interaction with Filemaker Pro's XML feed.

So for starters you need to think less like a MySQL Developer and more like a Filemaker Pro Developer. Use Relationships and Layouts to dictate the SELECT and FROM clauses in your SQL statement. Use the API to pass the WHERE and ORDER BY clauses. You could use calculated fields in Filemaker to get most of what you'd want out of a SQL GROUP BY clause (i.e. MAX(), COUNT(), etc) but it may be faster to use PHP to calculate those results from the returned data set.

also it sucks with larger record sets, and the time taken to construct the XML tends to increase exponentially - hence why it needs to be paged.

I strongly agree. If you want to routinely to utilize even medium sized data sets in both the Filemaker Client and on a web page, you are better off putting the data in MySQL and using MyODBC to create Filemaker "table references".

[Genx]

Thanks for saying what I didn't have time to logically construct : .. the proper sentences helped too :)