Table of Contents Introduction ...........................................................................................................................................................................................................1 About the Author ...................................................................................................................................................................................................3 Part I — Getting Ready for Web Publishing Create a Table ........................................................................................................................................................................................4 Create a Form .........................................................................................................................................................................................5 Create a Link ...........................................................................................................................................................................................6 Meta Refresh & Redirect.........................................................................................................................................................................6 Designing a Web Application ..................................................................................................................................................................6 Part II — Web Server Installation Apache on OS X .....................................................................................................................................................................................9 IIS Server 7.5 on Windows 7 ..................................................................................................................................................................9 Localhost vs. your Production Server ...................................................................................................................................................13 Part III — FileMaker Server Installation Single Server Model..............................................................................................................................................................................15 Configure FileMaker Server ..................................................................................................................................................................23 Craete a Backup Schedule ...................................................................................................................................................................23 Configure General Settings ...................................................................................................................................................................24 Configure Database Server ..................................................................................................................................................................26 Configure Web Publishing ....................................................................................................................................................................29 Testing Your PHP Installation ...............................................................................................................................................................32 Uploading the “php_demo.fmp12” Database File .................................................................................................................................33 PART IV — Choosing a RAD Tool vs. a Free Text Editor Dreamweaver ........................................................................................................................................................................................36 BBEdit ...................................................................................................................................................................................................36 TextWrangler ........................................................................................................................................................................................36 Nvu ........................................................................................................................................................................................................36 Zend Studio ...........................................................................................................................................................................................37 PART V — Introduction to PHP w3schools.com .....................................................................................................................................................................................38 php.net ..................................................................................................................................................................................................39 FileMaker Server 13 Custom Web Publishing with PHP ......................................................................................................................40 FileMaker API for PHP Tutorial .............................................................................................................................................................41 FileMaker API for PHP Examples .........................................................................................................................................................42 PHP Reference Guide ..........................................................................................................................................................................43 Formulations Pro PHP Site ...................................................................................................................................................................44 PHP Analytics Pro .................................................................................................................................................................................45 How PHP is Like FileMaker ..................................................................................................................................................................46

RecordID vs. Serial Number .................................................................................................................................................................47 Layouts, Not Table Access ...................................................................................................................................................................48 Naming Conventions .............................................................................................................................................................................49 Syntax ...................................................................................................................................................................................................49 Includes .................................................................................................................................................................................................49 Comments .............................................................................................................................................................................................50 Quotes ..................................................................................................................................................................................................50 Variables ...............................................................................................................................................................................................50 Concatenation .......................................................................................................................................................................................51 Operators ..............................................................................................................................................................................................52 If/Else ....................................................................................................................................................................................................52 Looping .................................................................................................................................................................................................53 Arrays ....................................................................................................................................................................................................53 Forms — $_GET, $_POST, $_REQUEST ............................................................................................................................................54 Part VI — Sample Database Table Structure .....................................................................................................................................................................................55 Fields ....................................................................................................................................................................................................56 Field Validation .....................................................................................................................................................................................57 Value Lists ............................................................................................................................................................................................58 Scripts ...................................................................................................................................................................................................58 Layouts .................................................................................................................................................................................................59 Portals ...................................................................................................................................................................................................60 Web Viewer ...........................................................................................................................................................................................60 Accounts and Privileges ........................................................................................................................................................................61 Security .................................................................................................................................................................................................62 Enabling PHP Access ...........................................................................................................................................................................62 Part VII — Publishing FileMaker Data with PHP dbaccess Include File ...........................................................................................................................................................................65 Container Bridge File ............................................................................................................................................................................66 PHP Info ................................................................................................................................................................................................68 Find All Records ....................................................................................................................................................................................69 Sort Records, User Defined ..................................................................................................................................................................71 Sort Records, Fixed ..............................................................................................................................................................................73 Search Records, Simple .......................................................................................................................................................................75 Search Records, Compound .................................................................................................................................................................77 Search->Detail ......................................................................................................................................................................................79 Insert Record ........................................................................................................................................................................................82 Insert Record with Validation ................................................................................................................................................................85 Delete->Detail .......................................................................................................................................................................................89

Edit->Detail ...........................................................................................................................................................................................93 Search Portal->Detail Portal .................................................................................................................................................................97 Insert Portal Record ............................................................................................................................................................................100 Edit Portal Record ...............................................................................................................................................................................104 Delete Portal Record ...........................................................................................................................................................................109 Display Container Field .......................................................................................................................................................................115 Display URL Image .............................................................................................................................................................................117 Upload File ..........................................................................................................................................................................................119 Display Externally Stored Container Field ..........................................................................................................................................124 Display a List Menu .............................................................................................................................................................................126 Display a List Menu Using Two Fields ................................................................................................................................................129 Display Search Results .......................................................................................................................................................................131 Create a Log In Procedure ..................................................................................................................................................................133 Log Out Inactive Users .......................................................................................................................................................................137 Alternate Background .........................................................................................................................................................................141 Run a Script — Update Records .........................................................................................................................................................143 Run a Script — Delete Records ..........................................................................................................................................................147 Run a Script — Send Mail...................................................................................................................................................................150 Display Record Pagination ..................................................................................................................................................................153 Display Record Navigation ..................................................................................................................................................................157 Multi User Process to Edit Records ....................................................................................................................................................161 Run a Script — Import XLS File ..........................................................................................................................................................165 Boolean Check Box ............................................................................................................................................................................171 Multi Value Check Box ........................................................................................................................................................................174 Multi Value List....................................................................................................................................................................................178 Pre Validate Data Before Update........................................................................................................................................................181 Perform Compound Search ................................................................................................................................................................185 Quick Find Across All Fields ...............................................................................................................................................................188 Google reCAPTCHA ...........................................................................................................................................................................192 Display a Random Banner Ad .............................................................................................................................................................194 Duplicate Record ................................................................................................................................................................................196 Duplicate Related Records .................................................................................................................................................................199 Install and Configure SSL to Work with FileMaker Server ..................................................................................................................204 PHP with AJAX ...................................................................................................................................................................................213 PHP Calendar .....................................................................................................................................................................................217 Compare Dates ...................................................................................................................................................................................224 Display HTML/CSS using PHP ...........................................................................................................................................................227 Expire Download Links .......................................................................................................................................................................229

Google Pie Charts ...............................................................................................................................................................................234 Google Bar Charts ..............................................................................................................................................................................237 Google Line Charts .............................................................................................................................................................................240 Google Map API Store Locator ...........................................................................................................................................................244 Google Map API Geocodes ................................................................................................................................................................248 Google Map API Map Markers ............................................................................................................................................................250 Jump Menu .........................................................................................................................................................................................255 Multiple Email Attachments.................................................................................................................................................................258 Submit Multiple Page Forms ...............................................................................................................................................................264 Print PDF Report .................................................................................................................................................................................268 Select and Update Multiple Records ...................................................................................................................................................271 Sub summary Report ..........................................................................................................................................................................274 Validate Credit Cards ..........................................................................................................................................................................277 Validate Phone Number ......................................................................................................................................................................283 cURL ...................................................................................................................................................................................................286 FTP with cURL ....................................................................................................................................................................................289 iOS Development ................................................................................................................................................................................291 Server Push Monitor ...........................................................................................................................................................................293 Slider ...................................................................................................................................................................................................296 Docx Mail Merge .................................................................................................................................................................................298 PDF Mail Merge ..................................................................................................................................................................................303 Display Image Gallery .........................................................................................................................................................................309 Display Pop Over ................................................................................................................................................................................311 Parse XML ..........................................................................................................................................................................................314 Related Value Lists .............................................................................................................................................................................316 Upload to Container ............................................................................................................................................................................321 Part VIII — Additional Information Extended FileMaker vs. PHP Syntax ..................................................................................................................................................324 Other Methods — PHP/ODBC, ASP, JSP, WebDirect .......................................................................................................................327 Costs ...................................................................................................................................................................................................329 Performance Considerations ..............................................................................................................................................................331 Security ...............................................................................................................................................................................................333 Error and Server Logs .........................................................................................................................................................................335 FileMaker Error Codes ........................................................................................................................................................................337 FileMaker API for PHP Reference ......................................................................................................................................................343 Index ...................................................................................................................................................................................................346

Introduction Welcome, and thank you for acquiring FileMaker API for PHP 13, A Practical Guide for Creating Database Driven Web Sites with FileMaker Pro 13 and FileMaker Server 13. If you’re reading this book I’m acutely aware that you have been challenged to publish data from your FileMaker Pro database to the web. Many of you have no web publishing experience, have experience on other platforms and technologies, and/or are frustrated by the lack of documentation, examples, third party products, and available rapid application development tools to publishing data from a FileMaker database. The FileMaker API for PHP has only been available since 2007. Therefore, I will assume that you are at least new to the FileMaker API for PHP and are seeking out resources to assist you to publish your data to the Internet in an efficient and professional manner. This book starts by demonstrating basic HTML that you should already be familiar with. If you are not already familiar with HTML you should seek out a book or other on-line resource to help you understand how to work with basic tables, forms, links, etc. When publishing data from a database you will be required to know how to create the tags such as headers, table rows, and table data to display your data correctly. If you can master this, you can create a dynamic web page to display your data. I don’t want to scare you right up front. It’s actually quite easy. However, like anything else, it does take practice and vision to know how you want the page to be displayed. Section II of the book deals with enabling either Apache on OS X or IIS Server on Windows. Both platforms support PHP. The nice part is that the FileMaker Server installation process will install PHP and turn everything on (for the most part). Your choice is simply to decide which platform you want to use to host your web site. The most important consideration for a server is to develop on the same platform on which your web site will be hosted, weather that is an in-house server or a third party hosted server. Although it’s not required, it does make testing and deployment that much easier. Part III discusses your FileMaker Server installation. FileMaker Advanced is no longer part of the product line, which saves a lot of money if you need to use the xDBC drivers. The PHP API does not require the use of any drivers. Unfortunately, you can’t use FileMaker Pro or FileMaker Advanced as a development tool when accessing the data via PHP. So you will have to break down and purchase FileMaker Server. In Part IV, this book covers the use of rapid application development (RAD) tools versus “free” text editors. You have to develop your web pages in some type of text editor. The question is how much money are you willing to spend to improve your productivity and extensibility. There are definite advantages to utilizing professional tools. Part V is where we start to dig in. It’s imperative that you learn some basic PHP. I’m not going to pretend to be an expert PHP developer as there is just too much to learn on top of everything else. However, a little bit of knowledge goes a long way. As in any programming language, you use maybe 80% of the available methods on a routine basis and you look up the other 20% only when you need to use them. This book is going to make you hand code PHP. Yes, I can hear you grumbling. The problem is that there are no complete RAD tools to do everything that you need for professional solutions. So it’s best to learn it the hard way the first time, then use the tools that are available to supplement your work. Part VI is an overview of the provided sample database. It does not go into excruciating detail about how to create a FileMaker Pro database. I’m going to assume that you already know how to do that. The database is very simple. It is used to demonstrate specific techniques for acquiring data from layouts, fields, value lists, portals, and how to run scripts. It does cover every possible scenario that you would build in a commercial application. I believe that it’s better to focus on the PHP methods rather than throwing a curve ball at you with unnecessary, complicated database structure or an esoteric topic. If you have a simple database to start with you can apply the same PHP code to far more complicated systems. If you are already comfortable with building a FileMaker Pro database and PHP you can skip right to Part VII. The lessons are broken down unto two basic sections. The Core section is what I consider to be the basic techniques and code that you will need to understand to build any successful web application. The Advanced section is just that — advanced topics and third-party API integration. Once you understand the Core topics you can pick and choose the Advanced topics that fit your needs. The Core lessons start off slow specifically for beginners. They demonstrate all the basic techniques to search, display, add, edit, and delete records. Then they graduate into more complex PHP and FileMaker methods to do things such as display container images, sessions, login routines, email, running scripts, etc. The main focus of this book is to provide complete, professional, production quality examples. You should be able to take these techniques and use them immediately in your own work. I will point out that there are many ways to accomplish the same end result. As such the lessons will create the content using a variety of methods so you can pick and chose the one that fits your programming style the best. The more techniques you learn, the better the programmer you will become. The book wraps up section VIII with reference material to other web publishing technologies that you can use with FileMaker Server. Next is the cost, which is always an important consideration. Then topics to consider for performance and security expectations. Where to find more information about logs and the FileMaker API for PHP reference guide as a last resort when things go wrong. Finally, the FileMaker PHP web site at www.formulationspro.com designed to be the leading developer community for all the PHP methods. User’s can freely access this repository of information and best practices code as well as share content and examples with other developers around the world. I have a few closing comments regarding the upgrade to the FileMaker 13 product line. There are no (complete) RAD tools to help you build your PHP code for use with the FileMaker API. That means you really have to learn how to program the API from scratch. The Server Admin console has been rebuilt using HTML5 and CSS. We no longer have to deal with Java breaking our ability to manage the server. However, FileMaker Server still uses Java under the hood. FileMaker Server now contains Web Direct (formerly known as Instant Web Publishing) and a new cost structure for connecting to the server with Web Direct and iOS (iPhone and iPad) devices. Fortunately, we’re here to do custom web publishing with PHP, which has unlimited access to the server. FileMaker also worked quite a bit on performance and stability with the web publishing engine and the web publishing core. You should see some minor performance gains with the upgrade. Outside of that the API is exactly the same. Lastly, I have spent an exhaustive amount of time re-testing every line of code. As it turns out, there are a whole slew of bugs in the various web browsers, web publishing engine, IIS Server, and PHP. All of these bugs have been noted and accommodated in this completely updated book. There are also 13 new advanced lessons added to the book. Therefore, even if you have the previous version of the book it may be well worth your time to study the new content in this book.

1

There are several conventions used in this book: Localhost Local host refers to your development server. The URL will typically be http://l127.0.0.1 DO NOT use http://localhost on a Windows server. Windows has problems trying to resolve localhost to 127.0.0.1, which can create major performance problems and even crashing issues. Production Server Production Server refers to your web server that is hosting the solution to the Internet. The URL will typically be http://www.mycompany.com Code Samples PHP code will be displayed in Courier font to make separating the code from the discussion easier. Special Content Special content or discussion will be called out to your attention.

Notes specific to the use of the FileMaker database will display the FileMaker Advanced icon.

Notes specific to the use of FileMaker Server will display the FileMaker Server icon.

Notes specific to PHP methods will display the PHP icon.

Notes not specific to anything will display the notes icon. Notes present an interesting topic that you can research further on your own.

Specific precautions that you should pay attention to. If you don’t you are bound to run into problems.

Specific instructions for Mac OS X only.

Specific instructions for Windows only.

2

About the Author Todd Duell is the CIO of Formulations Pro, Inc. and has been creating powerful custom and commercial solutions using FileMaker Pro since 1989. Todd holds an M.B.A. in Technology Management, is a Certified FileMaker Pro Developer, and has been a member of the FileMaker Business Alliance since 1998. Todd has published more than 500 technical articles, white papers, and open source databases to the scientific and Formulations Pro community of more than 3,000 worldwide subscribers. In Todd’s tenure at Formulations Pro since 1997, he has created more than 50 commercial databases and web-based applications for numerous worldwide, multi-billion dollar companies. Additionally, Todd is a regular contributor to Advisor Magazine, the industry-leading trade magazine for the FileMaker Developer community, and for the FileMaker Technet Resource Library at FileMaker Inc. Lastly, Todd frequently speaks at conferences and seminars on topics ranging from 21 CFR 11 (electronic records and signatures) to HIPAA compliance using FileMaker technology. On a personal side, Todd has been an avid Triathlete for the past 25 years, competing in more than 300 races with more than 100 top-3 age group finishes and numerous top-10 overall finishes. Todd has also finished 12 Ironman events, including the famed course at Kona, Hawaii. For 25 consecuritve years Todd placed first in his division at the Imperial Beach Championships. When he’s not training or racing, Todd can be found playing outfield for Magic Sports Unforgiven — a “Major Plus” rated men’s tournament softball team. This is the highest worldwide ranking a team can achieve. The teams that Todd has played for have played at the State Championships and World Series of Softball for a variety of associations every year since 2000. In 2010, Magic Sports won both the SSUSA California State Championship and the World Series. In 2011, Magic Sports placed second at both the State and World Championships. In 2014 Magic Sports placed third at the World Championships. In 2010 and 2011 Todd was named to the “All Tournament Team”, batting an impressive 0.760 and 0.780 average respectively.

3

Part I — Getting Ready for Web Publishing Creating web pages requires that you have at least a basic working knowledge of HTML. If you are already familiar with HTML you can skip Part I. Otherwise, I’ll cover just the basic HTML elements that will be included in all the lessons. That way you won’t encounter any surprises and will be able to refer back to this section if you get stuck for any reason. One of the things you’ll notice right away in the lessons is the simplicity of the code. There are no graphics or design, very little page structure, and the only apparent use of HTML is to display the data from the database. This is for a reason. I don’t want you to get stuck trying to weed through a complex page with images, CSS, and other elements. I want you to be able to focus solely on the PHP code to display information from the database. Once you learn the PHP code, the layout and design is easy. For now, the only special thing you need to know is how to display a PHP document. The file needs to have the extension .php. That tells your web server to process the PHP code on the page. Create a Table Data has to be placed into a table or DIV tag so it can be displayed and organized in a logical fashion. Without tables all your data will run together and end up as a big mess. Take a look at the sample code below and how it will display in a browser in Figure 1. You’ll see that the code creates a basic table with 2 columns and 2 rows. As in all HTML and PHP documents you have to start the document with the tags: , , , and . The tag tells the web server to process anything in the document and present it as html to the browser. The tag contains the title of the document that displays at the top of the web page. In this case I named the page “Sample Table”. Then I closed the head section by using the tag . All tags are closed by using the forward slash “/” with the tag name. Don’t forget to close the tags in the same order in which they are created or you will get some strange results. Sample Table First Name Last Name Todd Duell

Figure 1-1: Sample Table Next is the tag. This is where all the content for the web page is displayed. It will be your job to create the PHP code to output the correct html structure. For a simple table, which is used 99% of the time, you’ll start with the tag. Notice I included the attribute border=”1”. I did this because I wanted to table to draw a border with a line thickness of 1 pixel. There are a number of standard attributes that you can utilize to format the cells, including cell padding and cell spacing. Most of the time you’ll want to create a header row using the field names. To do this I created the first row with the tag. Then displayed the First Name and Last Name headers using the table header tags. The tag will display your headers with emphasis (i.e. bold) without actually having to use the tag. Don’t forget to close the first row with the tag. Then move on to the content using the same process as above. Except this time, the content will use the table data tag to display the table data (i.e. record data from the database) as plain text. Once you’re done with the table row you can close the tag as well as the tags for the table, body, and html tags. The lesson here isn’t how to create a table, table rows, table header, table data, etc. The lesson is to observe the repetitive nature of displaying HTML data. To put it in perspective; each record in the database will be wrapped in a tag. Each field will be wrapped in a tag. While you are looping through records and fields your PHP code will simply add the start and stop tags to the data from the database. That will enable a browser to correctly display the content.

4

Create a Form Forms will quickly become your best friend when working with data from a database. You will use forms to search for records and to submit data to create, update, and delete records. Inside the form you will be able to pass various pieces of information to the web server and database. The data can either be user entered, as in the Search field shown in Figure 2, or it can be hidden information that you don’t want the user to be able to see. All forms use the tag. The action attribute is the path to the document that you want to process the form. It can either be an absolute path such as http://www.mysite.com/Form.php or it can be a relative path to another document in another folder such as ../DifferentForm.php. Sometimes the form will point to itself and sometimes it will point to a different file. It really depends on how you want to set up your code for the user workflow. There are distinct advantages to both methods. Pointing the action to the same document means that you’ll only have one document to manage. However, the code on that one document may be quite long to handle a variety of branching user interactions. Pointing the action to a second document will allow you to process the form in a more discrete, logical fashion — similar to a sub-script in FileMaker Pro. The downfall is that you’ll have two pages in your site to manage.