ISO FileMaker Magazine: FileMaker Video Tutorials, Templates, Help & More

Making the big SQL jump
by Matt Petrowsky

Expanding your database horizons

While this editorial may be a bit close on the heals of a much read article about Servoy and SQL systems, it has come as a bit of a surprise to receive a number of requests asking for information about the database we are using for the magazine web site and which system we are running. In fact, we use both FileMaker and SQL systems for the various tasks at ISO FileMaker Magazine. MySQL is our backend database for the website, but for anything else I tend to use FileMaker Pro. While the heated debate can always arise about "eating your own dog food", I have long been a proponent of using the right tool for the right job, considering you have the right resources, skill and time to do so. Any SQL system, whether free or not, comes at a price when compared to FileMaker Pro. In this article I'll take a look at the advantages and costs (mentally and time wise) when it comes to running with another database system such as MySQL. I'd like to thank Dan Scheer and other subscribers for writing in about the new magazine and it's structure. For reference, here is what Dan asked.

"I had a question about your website. I noticed that it has some dynamic
scripting going on using PHP. What database is running your website? I
hate to say this, but the speed makes me think it is not FileMaker.
Could you elaborate? My biggest problem with getting a FileMaker host
is the price and db limits. I was planning a move from FM/Lasso to
MySQL/PHP for price and speed, but I still need local/non-internet
connected access to the data. Any suggestions?"

Thanks,
Dan Scheer

Great question, are you prepared for the answer?

To give you the short answer, Yes, we are using MySQL/PHP. In fact, the magazine web site has used MySQL/PHP since 1997. To answer the "Any suggestions?" part of Dan's question, I'll have to write this article. First, you have to realize that the price you pay for database hosting is relative to what you might be expecting. When considering price alone, you can find more raw power in a *nix (short for Unix/Linux, etc.) based solution than a FileMaker based web solution. If you are expecting that FileMaker hosting should be cheaper and more available than MySQL hosting then you'll be hard pressed to find competitive rates. The reason for this is quite simply supply and demand. There's more demand for *nix based hosting than there is for FileMaker based hosting. And while *nix based hosting is readily available, you might want to consider what you have ahead of you. It's likely going to require you to...

Be prepared for a 500% increase in learning curve

No joke here. When learning how to use FileMaker to host a web solution the process can be a bit daunting at first, but it's something that can be eased into. The first biggest hurdle is finding the information and resources when you hit a problem you can't solve. Unfortunately, the ratio of developers using FileMaker for web hosting compared to people using PHP/MySQL or even ASP or WebObjects is very low. On the other side, with PHP, there are many thousands of developers, so you'll find a lot of learning material. I've included a number of resources I use at the bottom of this article. With FileMaker, there are those pockets of quality web related content and FMWebSchool is one of them (see http://www.fmwebschool.com/ ), we also offer a free discussion list named fmwebtalk. Send email to fmwebtalk-subscribe@filemakerworld.com to join the list.

If you don't already have a firm grasp on the fundamentals of web hosting and how this relates to a database let me explain. There are essentially three parts to web hosting using a database - note, I'm not including the client/browser as part of this equation. First is the web server. This includes the popular servers such as Apache on OS X/Unix/Linux and IIS (Internet Information Server) on Windows, plus other servers such as Tomcat, Stronghold, Netscape Enterprise Server and others, each with enhancements in certain areas. The second part I'll mention is the "backend", the database, this is either a SQL system like Oracle, MS SQL, Informix, Sybase, DB2, MySQL, Postgres, Frontbase, Firebird or any other I forgot to mention, or it may be FileMaker. So we have two parts of a three part equation, and here's an easy way to think about the first two, they are the buns to your burger. They simply sit on either ends of the web serving equation. Just think server on the left, database on the right.

It's the "stuff that's in the middle" that makes up the third part. Whether you're a vegi-burger lover or you go for a 100% ground round, the stuff in the middle is where the action is, and there's more going on than just the meat. For FileMaker this is either CDML or Lasso almost 90% of the time, although you can use PHP (see http://www.iviking.org/ ) for the connection between web server and database. For Apache servers this is typically PHP or Java, and for IIS on Windows you'll find ASP, Coldfusion or Java, along with a few others in the lower percentages of use. Web Objects is also an option as "the stuff in the middle" and will connect to SQL systems, and has also been connected to FileMaker (see http://www.360works.com/ )

So what's so hard about it all?

Well, with FileMaker you start with a visual environment where you pick and choose items from a palette or list and you add them to your solution. This includes layout objects when making a layout and ScriptMaker script steps when building a script. Even when using FileMaker for the backend of your web site you have to first jump into learning a more linear "code like" language and that is HTML. To the web, HTML is like Layout mode to FileMaker. It is what creates the presentation of data in the database. Along with having to learn the many intricacies of HTML and what it's limitations are, you have the stuff in the middle that has it's own "language" which you also have to learn. So far, this isn't too much for a CDML or Lasso developer. Incorporating special tags within HTML is what CDML or Lasso is all about.

With FileMaker you choose either CDML, which comes with FileMaker, or you opt for the more feature rich language such as Lasso (see http://www.blueworld.com ). Lasso, or LDML as it may be called, is simply an extension to what CDML currently provides in FileMaker. Since CDML was the original Lasso before Blueworld and FileMaker had a falling out, the technology was expanded only slightly by FileMaker and unlike PHP, was not designed from the ground up for web purposes. You have to keep in mind that when PHP was created it was created for the web. When CDML was created it was created as an add on to what was primarily a desktop database. In fact, for you old timers it was FileMaker's Apple Event hooks that started with Web FM and the first versions of what is now Lasso. But on with what you need to know.

On top of learning HTML, the difficulty with learning PHP, which is the middleware "stuff in the middle" language, is the fact that it comes in the form of a C-like structure and syntax. You've now left the world of your comfortable visual environment that is FileMaker and have opted for a collection of text documents which include code that looks similar to this - and many times, a bit more complex.

if ($artpage==1) {
    /**
    * article page output
    */
    global $info, $hometext;
    echo "<title>$info[title] :: ".pnConfigGetVar('sitename').' :: '.pnConfigGetVar('slogan')."</title>\n";
    if (pnConfigGetVar('dyn_keywords') == 1) {
        $htmlless = check_html($info['maintext'], $strip ='nohtml');
        $symbolLess = trim(ereg_replace('("|\?|!|:|\.|\(|\)|;|\\\\)+', ' ', $htmlless));
        $keywords = ereg_replace('( |'.CHR(10).'|'.CHR(13).')+', ',', $symbolLess);
        $metatags = ereg_replace(",+", ",",$keywords);
        echo "<meta http-equiv=\"Keywords\" content=\"$metatags\">\n";
    } else {
        echo "<meta name=\"KEYWORDS\" content=\"".pnConfigGetVar('metakeywords')."\">\n";
    }
} else {...[more here]

Now, if you aren't scared easily by the initially confusing combination of both HTML and PHP syntax globbed together in the same file then you'll need to pay attention to the fact that it's not just HTML and PHP that you have to know. It is also the *nix file system and it's method of managing permissions, it's Regular Expressions (such as those found in the middle of the code above where the trim(ereg_replace(...) appears, and it's the Apache Server and a it's configuration file named httpd.conf, and don't forget to throw in the little control files that Apache uses called ".htaccess" files. This doesn't even include various supplements to Apache such as mod_rewrite and other modules that control what your "stuff in the middle" can do with the server. Add on any extended technologies such as XML and you have about fiver layers of unique environments that you need to learn for a solid understanding of the whole web serving picture.

The good news is that many of your functions that you have learned in FileMaker are also found in PHP and other languages. This is the good news. The bad news is that many of them do not directly map over. The trim() function above, for example, does the same thing in both environments. However, PHP does not have a RightWords() function and does have many MORE functions (see http://www.php.net/manual/en/funcref.php ) than FileMaker Pro. In fact, there are many hundreds of possible functions. The trick is knowing, or learning, which ones you need to know. The key here is understanding that logic, whether in FileMaker or PHP, is logic. You always need to know the flow control statements and operators. These are the ever popular If() and Loop(), although in PHP you have three different kinds of loops, for(), while() and foreach(). I have to be honest and tell you that the bulk of my learning, when it comes to logic, came from working with FileMaker Pro back in 1991. Surprisingly, once you learn logic somewhere it doesn't change when you move to another language. It's the language, syntax and implementation that changes. You just have to have the guts to invest the time learning the new stuff.

Yet another possible scenario you might come across is having to learn how to compile an application, such as PHP, if the current configuration does not support the features you are looking for. The time and place where you might do this is on your own web server. Typically, if you are using a hosting provider they will offer PHP and MySQL but you will not be able to change the core configuration of PHP. Rarely, some hosting providers do allow you to install the CGI version of PHP, where many hosting providers tend to run the module version of PHP for Apache. My recent situation dictated that I recompile PHP on my Mac OS X box because I needed to add support for using XSLT for transformation of XML documents. The transformer that PHP uses is Sablotron (see http://www.gingerall.com/ )and I had to first download and compile that application before I could recompile PHP.

So, as you can see, there is far more to using PHP/MySQL than a FileMaker developer might think. Learning another language such as ASP to work with MS SQL has yet it's own syntax and environment. What you pick is tied to the environment you'll be working in. If you're overwhelmed, like I was, one of the things I find helps me a lot is telling myself "You can't know it all so start with one small piece and work from there."

Where's my Define Fields?

So now we have working with the database itself. In our situation the web site is running on MySQL and could very well be using any other SQL database. For the most part, SQL is SQL. It is just a matter of what variations Oracle has over MySQL with regards to the implementation of SQL. If you want to modify, look at or work with the structure of a MySQL database then you have to have another application or tool to do this. The default command line tools that come with the base install are just too boring - although very effective at certain tasks such as backing up a database. One of the most popular tools for MySQL is called phpMyAdmin (see http://sourceforge.net/projects/phpmyadmin/ ) This is a PHP based solution that allows you to manipulate a MySQL database. On the application side of things there are a number of commercial and freeware solutions for both Windows and Macintosh. Since I am familiar with working on a Macintosh due to it's BSD underneath I use these applications SQL4X (see http://www.macosguru.com/ ), YourSQL (see http://www.mludi.net/YourSQL/ ) and CocoaMySQL (see http://cocoamysql.sourceforge.net/ ).

Learning how to get into your MySQL database is yet another learning experience and it surprises some FileMaker developers that there is no such thing as Calculations or Relationships in a SQL database system - at least not in the FileMaker sense. There are relationships that use a primary and foreign key but they don't work like they do in FileMaker Pro. Actually, a FileMaker relationship is very much like a SQL SELECT statement. The portal that you view information in is actually a request, or SELECT query, from another FileMaker table based on the fields used in the relationship. So here comes another item to learn - SQL statements. However, I can demystify this one a bit for you. There are only so many statements in SQL. When working with a database these are simply SELECT, INSERT, UPDATE and DELETE. There are a few others that deal with working with the SQL system as a whole and these are CREATE, ALTER, INDEX, DROP, GRANT, PROCESS, RELOAD, SHUTDOWN and REFERENCES. For the most part, that's all there is to getting started. When you get into learning more SQL you find that rather than using Calculation fields, SQL uses it's own SQL Engine to make sense out of the data. The engine is one of your first levels of logic. This is where you get various functions that relate to the data. Some random examples would be DATE(), ABS(), ROUND() and others (see http://www.mysql.com/doc/en/Reference.html ). So this prompts the question, "So you're saying I can use the ABS() function within MySQL and within PHP?". Resoundingly, YES. This is where the speed and power comes from. You have multiple levels of control wherein lies yet another level of learning. Learning where it is best and or fastest to exert you control over this power. A lot to learn isn't there?

Starting from scratch or leveraging history

Ok, you got me. I didn't build the magazine web site from scratch. In fact, as many have guessed I used an Open Source CMS (Content Management System). I'll tell you which one in a few sentences. Before that, let's take a look at coding history. One of the biggest selling points for Microsoft's.Net strategy, and likewise the Open Source movement, is the fact that you don't have to write as much code to get things done. The fact of the matter is this, how many times do you need to write a routine that converts Celsius to Fahrenheit? Once, hopefully.

In the less connected world, prior to the Internet, unless you had a developer friend who had created this function, you would have to learn how to do it yourself. In this new and improved connected world we live in, the fact that code already exists, and is available, is what saves you the time from having to write it yourself. However, the difference between knowing how to implement and understanding what is going on underneath is a big gap. Using PEAR from PHP (see http://pear.php.net/ ) I can make a call to any number of classes that have a wide range of functionality. Classes are simply abstractions from the code you would typically have to write yourself. You leverage these classes by making calls to them and you gain the functionality of what you might have had to program without actually having to write it yourself. The same applies to a full system or solution. In the case of the popular CMS category, there are many you can choose from. Doing the research can be overwhelming, as I found out when staring my initial research. One thing to note is that the system is rarely going to be exactly what you want and will require ongoing tweaking. The system I started with is called Postnuke (see http://www.postnuke.com/ ) and has been around for a short while - relative to CMS systems. My advice with getting started is to just pick one and install it in a test environment. Use it to learn PHP. It's a great place to start if you're a self-motivated leaner.

One of the funny things about Open Source CMS systems, and the reason that some developers, those not in the "know", don't consider it stable, is because when it comes to CMS systems it's not - it's in flux. Don't confuse all Open Source CMS systems with the wide range of what is covered under the Open Source moniker. Obviously, PHP, which is also open source, is quite stable. Postnuke is what they call a "forked" version of it's predecessor PHP-Nuke (see http://www.phpnuke.org/ ) and a variant version of this same code is happening in a newer fork called Xaraya (see http://www.xaraya.com/ ). Things are always changing in the Open Source world of CMS systems because it is either a group of individuals with a commercial interest (who promote and sell hosting) or it is developers who have full time jobs and enjoy the ego satisfaction that comes with contributing to a larger project that potentially becomes a great CMS. The number and variety of developers who contribute to a project in Open Source may be as diverse as the minerals in a mountain side. Coding styles vary and you can learn a lot from the variety of ways a particular issue can be solved.

There are actually many open source and pay-for CMS systems. Here is a list of a few others. Typo3.org (see http://typo3.org/ ) GeekLog (see http://www.geeklog.net/ ), Drupal.org (see http://www.drupal.org/ ) and MamboServer.com (see http://www.mamboserver.com/ ). One thing to keep in mind when choosing a system is the fact that Open Source quite literally means OPEN. This is a great advantage to the developer who wants to learn about all the topics I am discussing. Rather that feeling like you are starting from scratch, you are staring with a collection of code that reveals the wealth of knowledge from quite possibly hundreds of other developers who have made suggestions and contributions that have found their way into these systems. Once you learn one, making the jump to another is quite easy if you have the fundamentals covered. Another great place to find a lot of PHP scripts is at HotScripts.com (see http://www.hotscripts.com/PHP/ ).

When is FileMaker a good fit for the web?

So the big question is this, "When do I use FileMaker and when do I jump to MySQL?". As I pointed out in my article about Servoy this all depends on what you have available, your server, and what you know - in terms of comfort level. Take these items and join them with what your intended outcome and audience is and you should be able to make an educated decision. Knowing Lasso alone and thinking it is the ultimate solution for everything will quickly reveal many holes in your thinking. This isn't a knock against Lasso, in fact, I cut my teeth with Lasso 3 and learning the WebStar configuration made it easier to understand Apache's httpd.conf file. My point is the more you know, the more capable you are.

FileMaker Pro, as a database backend for the web is an extremely viable solution for most situations where 1) you control the server, 2) can optimize the hardware, 3) aren't sharing with hundreds of other FileMaker solutions and 4) know that you won't likely scale to handling more than a few tens of thousands of hits a day. If you need to be up and running in a short period of time and the language you choose, CDML or LDML, will provide you with the functionality you need, then stick with FileMaker. Of course, this is for today's version of FileMaker. There are, according to those who use the Web Companion on a daily basis, (the built in web server in FileMaker) some known issues with high hit counts. In the future, it's likely that FileMaker, Inc. will move from CDML to some other technology since they have made it somewhat obvious that the outdated CDML language is not being updated. If you're currently using CDML then don't mistake this article for a blatant statement like "You're silly for using CDML" rather use it as an enticement to learn about the other web possibilities.

If I personally had a Windows or Macintosh server co-located where I had pretty good access to it, then I might opt to use FileMaker for a site that might be a small, medium to medium-large sized site. In fact, I know of a number of places where an optimized FileMaker server using Lasso handles hundreds of thousands of hits and scrapes the 2GB size limit on files. However, in my situation I am using "rented" servers where I get redundant power and a certain amount of bandwidth per month. This requires that I be responsible for configuration, security, backup and all the other aspects that go into hosting our own web sites on Linux servers. Because I am comfortable with the Linux environment this is not a deterrent for my situation. Plus, it pays to have friends and co-workers with strong Linux skills. Aside from the fact that I enjoy learning the myriad of technologies, the track record for a Linux server typically surpasses both Windows and Macintosh. As of today, our server has been running flawlessly for 197 days straight and the reason for a reboot 197 days ago was probably some type of install that required it.

Once you get used to working with MySQL and PHP, and you learn the ins and outs of being comfortable in a command line world, your ability to repurpose code is the same as being able to make a copy of a FileMaker file and using it in another solution.

For testing purposes, if you aren't running Mac OS X, where you can install MySQL and PHP quite easily using installers from http://www.entropy.ch/software/macosx/ , then your best bet is grabbing a free copy of a Linux distribution. This will help you become familiar with what most ISPs are running. Although, there are versions of PHP and MySQL that will run on Windows computers. In my case, I recently downloaded Red Hat 9 (see http://www.redhat.com/apps/download/ ) from having used Mandrake 8 (see http://www.mandrakelinux.com/en/ftp.php3) for a while. Installing Linux on one of your older Intel computers is a great way to get started. However, be prepared to spend many, many months learning all kinds of things from basic command line skills to where things are stored and how they are configured.

Why they compliment each other

One of the great features of using both FileMaker and a SQL system is that you can use each for what they do best. There is no way you can create a powerful user interface using HTML within a web browser when compared to FileMaker Pro. And there is no way you can expect the same raw speed and power from FileMaker when compared to MySQL and PHP. With cross-over technologies such as XML you can build a MySQL/PHP publishing system for web delivery and use FileMaker Pro to create a front end information delivery tool.

Follow this scenario with me. Imagine, if you will, a super large corporation where they've standardized on Oracle. All the sales information that a sales force needs access to is all on their corporate intranet. The sales reps need to print customized reports and need functionally built into their application much quicker than can be done by the Oracle/Java programmers. The solution is to use XML and create a standard that allows the Oracle system to provide a "feed" to a client application, such as FileMaker, that can use XML/XSLT to bring that information down off the corporate intranet into a desktop database that can slice through the data like a hot knife through butter. FileMaker Pro is king when it comes to manipulating data quickly and with a level of finesse that I have found in few environments. I have so many FileMaker databases designed to be used as tools that it would take me a week to find them all on my computer.

For example, RSS, a specified XML format for news syndication, has taken the web by storm and provides only a glimpse of what is to come when tieing a powerful client to an open standard on the backend. There is a place for both. One caveat however, I have found a unique situation in the world of FileMaker, and this comes from many years of experience and exposure to FileMaker systems.

Due to the simplicity of using FileMaker Pro, there are times when a dedicated FileMaker developer will push FileMaker into a role where it is quickly overburdened, and this is due to the lack of knowledge by the developer, of how other systems are more optimized for heavier loads. Through this article, it is my intent to provide you with the mental coaxing needed to step outside of your comfort zone should you wish to understand technologies beyond just FileMaker Pro. The evolution of and increased demand for database systems by our global community will require database developers, FileMaker or not, to be more knowledgeable about technologies outside of what they may currently know. In the case of communicating data from one point to another I urge developers to take the first step towards the sometimes scary world of XML/XSLT. FileMaker currently knows both and if FileMaker, Inc. is wise, they'll take advantage of this in the future.

When I don't have the time to learn all this stuff

While this article is far from an advertisement and is designed to give you information about installing and running a CMS site, you may not want to get into the gory details of learning *nix and installing the software required. If this is the case, then you'll be pleased to know that there are hosting providers out there who will host your site for you. While you may find a wide range of both services and prices it pays to know that who you are dealing with knows their stuff and will provide you with an increasing level of enhancements as the technology grows over time. If you would like to use ISO to help host your own publishing site then please send email to matt@isoproductions.com. We are looking into offering a range of services related to commercial publishing via the Internet. Our experience has led us to find out if the market sees this as something they could use.

Taking it one day at a time

This familiar saying has helped me recently, as I have become actively involved in the day-to-day operations of the magazine. Having been away for a while, I have come to appreciate the rewarding experience that is working with a variety of technologies outside of just FileMaker Pro. While I will probably always find a fit for FileMaker Pro somewhere I found it compelling to bring this type of information to you. We don't live in an isolated world and FileMaker Pro will touch as many of these other technologies, such as SQL and XML, as time and businesses move on.

As always, I welcome your feedback and questions. Looking for a particular solution? Have a FileMaker itch? Send them to me at matt@filemakermagazine.com.

Resources

There are an overwhelming number of places on the Internet where you can gain insight into the topics covered in this article. Below are a list of the books I have on my shelf and the web sites I use.

These are the books on my shelf that relate to this article.

PHP4 Developer's Guide - Blake Schwendiman (McGraw-Hill)

PHP Cookbook - Sterling Hughes & Andrei Zmievski (Sams)

MySQL - Paul DuBois (New Riders)

XSLT & XPATH "A Guide to XML Transformations" - John Robert Gardner/Zarella L. Rendon (Prentice Hall)

Inside XSLT - Steven Holzner (New Riders)

Linux in a Nutshell - Jessica Perry Hekman (O'Reilly)

Web sites to use

PHP Builder http://www.phpbuilder.net/
PHP.net http://www.php.net/
MySQL http://www.mysql.com/