How to Delete a Found Set With FileMaker.php
September 2, 2009
Unlike a typical SQL database, FileMaker's PHP API doesn't support anything like:
DELETE FROM myTable WHERE status = 'Inactive'
This means that if you wanted to delete a bunch of records with PHP, you'd have to do a find and then loop through the records deleting them one by one. This would take forever and probably overwhelm your server with requests.
Fortunately, the FileMaker API for PHP does allow you to call internal FileMaker scripts. Calling a FileMaker script to find and delete a bunch of records is way faster than a looping delete.
Here's an example FileMaker script that would delete any records from the Product table with a Status equal to whatever value is passed in as the script parameter:
Go to Layout [ “Product” (Product) ]
Enter Find Mode [ ]
Set Field [
Product::Status; "==" & Quote ( Get ( ScriptParameter ) )
]
Set Error Capture [ On ]
Perform Find [ ]
Set Error Capture [ Off ]
If [ Get ( FoundCount ) ]
Delete All Records [ No dialog ]
End If
Show All Records
Show Omitted Only
There are a few things to note about this script:
- I turned on error capture for the Perform Find step so that if no records matched the request, 0 found records would be returned. Had I not done that, the results would be unpredictable when no records matched the request.
- In a production solution, I'd add error checking to ensure that I had the right found set before running the Delete All Records step.
- I make sure to omit all the records at the end of the script so they don't get returned to PHP.
Don't forget that last one; it's a doozy. When you run a FileMaker script with PHP, FileMaker sends back the found set when the script completes. And remember, when you have a found set and delete it, FileMaker Pro doesn't leave you on an empty set; it does a Show All after the delete. If you have a lot of data, returning all those records to PHP could take an eternity.
Here's what the PHP code that calls this script might look like:
<?php
For security reasons, these lines should be
included from a file above the web directory
define('FMHOST', '127.0.0.1'); define('FMFILE', 'ProductDB'); define('FMUSER', 'webUser'); define('FMPASS', 'webPass'); #
Connect to fm
requireonce ('FileMaker.php'); $fm = new FileMaker(FMFILE, FMHOST, FMUSER, FM_PASS); #
Init some vars
$layout = 'Product'; $script = 'DeleteProducts'; $param = 'Inactive'; #
Define the script request
$request = $fm->newPerformScriptCommand($layout, $script, $param); #
Execute the script
$result = $request->execute(); #
Check for errors
if (FileMaker::isError($result)) { die($result->getCode() . ': ' . $result->getMessage()); } #
We out
echo 'The records have been deleted';
Please bear in mind that this code is intended to introduce you to the technique of calling FileMaker scripts with PHP. In a real solution, you'd want to beef up the error checking and consider the security implications. If you have thoughts about what improvements you would make, please post them in the comments.
Calling FileMaker scripts from the web is a very powerful technique that allows you to expose your FileMaker database to the world as an API or web service. If you are interested in this sort of thing, you might want to take a look at RemoteScripter from 360works. I have not had a chance to try it yet, but it sounds very quick and powerful. If you have tried it, please leave a comment with your thoughts.
Cheers!
