Skip links

Search engine optimization (SEO) with FileMaker and MySQL.

FileMaker training, courses, coaching

FileMaker_SEO_article_imageSearch engine optimization and FileMaker may not seem to go together at first glance. On closer inspection, however, FileMaker turns out to be an excellent tool for populating websites and online stores based on MySQL, for example, with search engine-optimized meta tags such as title, description or keywords. Using the well-known online store system xt:Commerce as an example, I will show in this article how FileMaker can be used very well to provide a large number of articles with optimized information at the push of a button. The "normal" way would be to call up each item individually in the backend of xt:commerce and add the details manually. Of course, this is also possible, but we don't have time and want a faster solution...

Importing article data into FileMaker

In the article FileMaker and MySQL I have already described how FileMaker works with a MySQLDatabase can be connected to the Internet. This article assumes that you have a fully configured ODBC-Interface to your web server on which the xt:commerce MySQL database is located. Let's first take a look at the tables in which xt:Commerce stores relevant article data:

products - In this Table is the article master with the Primary key <>. The table stores data such as the article number, product name, status, VPE, image link, etc.

products_attributes - This table stores the product attributes assigned in xt:Commerce for the individual products - for example, "Size: o M o L o XL", which the customer can select in the product before ordering. This table is irrelevant for search engine optimization.

products_description - This is the relevant table that we need for search engine optimization. It contains the description (long article text in HTML format), the short description in HTML format, the name of the article and all meta information (search terms, meta keywords, meta description, meta title).

xt:Commerce has a few more product-related tables such as products_options, products_to_categories or products_xsell, in which further information on articles is stored, but which are not relevant for our article.

If the ODBC data source has been successfully set up on your computer, as mentioned above, you now create a new database in FileMaker:

File > New database...

Enter a name of your choice, e.g. "fmseo.fp7". When the window for the database definition opens, close it, because we will now automatically import our data tables from xt:Commerce into FileMaker. For the sake of simplicity, we will only work with the products_description table from xt:Commerce in this article. If you have a complete Article management you will certainly also need the other tables - but this would go beyond the scope of this article. To import the desired table into FileMaker, select the following menu item:

File > Import data records > ODBC data source

A dialog will then appear in which you can select the ODBC data source you have created for the xt:Commerce store:

Select ODBC data source

Enter the user name and password for the MySQL database in the following dialog:

ODBC password entry

As soon as you have successfully entered your access data and confirmed with the [OK] button, the dialog for creating a SQL-query:

SQL query creation

In this dialog, in the "Tables" column on the left, select the table products_description from. Then simply double-click all fields in the right-hand column "Columns" until your SQL query is complete in the text field below. The SQL query should now look like this:

SELECT `products_description`.`language_id`, `products_description`.`products_description`,
`products_description`.`products_id`, `products_description`.`products_keywords`,
`products_description`.`products_meta_description`, `products_description`.`products_meta_keywords`,
 `products_description`.`products_meta_title`, `products_description`.`products_name`,
`products_description`.`products_short_description`, `products_description`.`products_url`,
`products_description`.`products_viewed`
FROM `products_description`

After clicking on the [Execute] button, the SQL query is executed on the Server and the familiar dialog for assigning the import fields from FileMaker now appears. As no table yet exists in the new database, select "New table" in the top right-hand corner under "Target".

Assignment of import fields in FileMaker

After clicking on the [Import] button, FileMaker now creates a new table with the original field names of the table from xt:Commerce and simultaneously imports all data records in this table. You have now created a new FileMaker database containing the products_description table including all records from xt:Commerce without having to create a single table or field definition in FileMaker!

If you now adjust the fields on your new layout a little according to your wishes, your database could now look something like this:

Layout of the FileMaker database

In this new database, you can now maintain meta tags or other details for your articles to your heart's content. Of course, you also have the option of having meta data calculated automatically. For example, you can also import the article master table products from xt:Commerce and link it to the already created table products_description via the primary key <>. In this way, you can automatically fill fields with content using automatic calculation ("Replace field content"), for example. There are practically no limits to your possibilities!

How to get the data back to the MySQL server

If you have now successfully edited or created the data in your FileMaker database, you naturally want to write the data back into the online store. We can do this very conveniently directly in FileMaker as a Script realize. In this way, you also have the option of updating either one (the selected) data record or all selected data records simultaneously.

To write data to an SQL server, FileMaker provides us in ScriptMaker with the command

Execute SQL Query

is available. For example, to update the product description, i.e. the <> field, on the SQL server, we need the following command in ScriptMaker:

Execute SQL Query [Without dialog box; DSN: YourMySQLServer; Calculated SQL text:
"UPDATE `products_description` SET `products_description` = "" &
EXCHANGE(xtc_products_description::products_description; """; "") & ""
WHERE `products_id` = " & xtc_products::products_id]

To avoid compatibility problems with the SQL query, our script command automatically exchanges double quotation marks (") for single quotation marks ('), which may be in the description text. This simple script command updates the <> field on the SQL server with the content of the field of the same name in your FileMaker database. Of course, this works with all fields in the database, including all meta tag fields.

If the field of all called records in FileMaker is to be updated on the SQL server, this can easily be realized by a loop, for example like this:

Loop (start)
Execute SQL Query [Without dialog box; DSN: YourMySQLServer; Calculated SQL text:
"UPDATE `products_description` SET `products_description` = "" &
EXCHANGE(xtc_products_description::products_description; """; "") & ""
WHERE `products_id` = " & xtc_products::products_id]
Go to data/query/page [Next(r); End after last]
Loop (end)

You also have unlimited options when it comes to updating data records on the SQL server and can easily integrate these functions into your user interface. You don't need to be a SQL crack to use the SQL queries - the solution shown here only requires very simple SQL queries. To transfer existing data from FileMaker to the MySQL database, you only need the SQL command UPDATE. Use the WHERE restriction command to ensure that only the record whose primary key <> corresponds to the record called up in FileMaker is updated.

I hope that the tutorial is understandable and comprehensible up to this point. In another article I will show how to write new data from FileMaker to a MySQL database - for example to generate delivery statuses or other data from FileMaker directly in MySQL.

Leave a comment

  1. Thanks for the interesting report!

Share this page:

ERP software as flexible as your company.
We will be happy to advise you.

Customizable ERP software for Mac, Windows and iOS.

You are here: SEO with FileMaker and MySQL using the example of xt:Commerce