WordPress is the most widely used content management system in the world today, and many websites in Germany are also based on it. There are countless plugins and other extensions available for WordPress. The WordPress plugin "Contact Form 7" has been established for years as a means of providing forms for user input. At first glance, the plugin may seem a little more rudimentary than some other "bloated" plugins, but it scores highly in terms of performance. In order to send form entries from users not only by e-mail, but also in a Database you can use the free WordPress plugin "Flamingo", which comes from the same manufacturer. In this article, we go one step further and transfer the saved data to a FileMaker database at the touch of a button. To run the sample FileMaker database included in the article with direct access to the WordPressServer is the MBS FileMaker Plugin required.
Access to the MySQL server with the MBS plugin
All databases of a WordPress installation are located on a MySQL database of the corresponding web server. In order to be able to access the database externally, the corresponding database must be released for external access. This setting can usually be made in the backend of the server provider.
In order to access the web server databases directly, it is necessary to use the MBS FileMaker plugin, as FileMaker does not allow direct access to the databases with on-board tools. SQL-server. The MBS plugin can be installed on the Website of Monkeybread Software and a free demo version is available for download. To enable access to the mySQL databases, the MySQL library (libmysql.18.dylib or libMySQL.dll) must also be located in the database directory.
WordPress, Contact Form 7 and Flamingo
To provide forms for user input on a WordPress-based website, use the plugin Contact Form 7. To save the form entries, also install the extension Flamingo from the same manufacturer. Both plugins can also be installed and activated directly in the WordPress repository within the WordPress backend.
In the WordPress backend, navigate to the menu item [Forms] and create a new form for your website. Since Flamingo automatically saves all form entries, you don't have to do anything other than create a new form.
Flamingo saves all data records internally in the Table "wp_posts" with the post type "flamingo-inbound" and does not create its own table in the WordPress database. In our small example solution, all data records are imported from the "wp_posts" table with the "flamingo-inbound" post type. Because the data records are simply separated by Flamingo using a line feed, we can easily extract the field contents using the FileMaker function "GetValue".
In principle, other data records can also be imported from the WordPress database by specifying a different post type. The data could then be stored in the [post_content] may be formatted differently. In our example solution, we will limit ourselves to WordPress with Contact Form 7 and Flamingo, which simply writes the field contents one line below the other.
Screen of the sample solution
In the example database, the left-hand column contains all the fields for defining your WordPress database with all the required fields.
Enter the following data in the fields so that you can import data from your WordPress installation into FileMaker. All data can also be found in the "wp-config.php" file, which is located in the root directory of your WordPress installation.
- Server address (Host)URL under which your server is accessible, on which the database for WordPress is located. Attention: In order to be able to access the database externally, the corresponding database must be released for external access. This setting can usually be made in the backend of the server provider.
- Database: Name of the database in which all WordPress tables with the prefix "wp_" are located.
- User nameUser name for logging into the database.
- passwordPassword for logging into the database.
- Post-TypeWe use the post type "flamingo_inbound" in our example. In principle, other data records can also be imported from the WordPress database by specifying a different post type.
Field definitions of the sample solution
Our example solution uses two tables:
- SettingsContains all fields for the server settings as global fields
- FormsContains all fields for the imported forms
A relationship between the two tables is not required because all setting fields have been defined globally.
The table [Forms] contains a calculated text field with the calculation for each target field:
- GetValue (post_content; x)
The placeholder [x] in this case stands for the number of the entry. As all entries generated by Flamingo in the [post_content] are separated with a carriage return, the fields can be easily extracted using the "GetValue" function.
Scripts in the sample solution
The sample solution contains two scripts: One Script for establishing a connection to the MySQL database server and a script that can be executed via the [Data import] is executed. The partial script [ts.MySQL_OpenDatabase] is used at the beginning of the main script [sf.MySQL_Import_wp_posts] called.
- If [NOT IsEmpty(Settings::wp_Database) AND NOT IsEmpty(...)]
Queries whether all settings fields have been filled in.
- Execute script ["ts.MySQL_OpenDatabase"]
Executes the partial script for opening a database connection with the MySQL server. The partial script returns a number for the connection.
- Variable set [$Connection; value:Get(ScriptResult)]
Writes the returned number of the connection to the variable $Connection
- Set variable [$Command; value:MBS("SQL.NewCommand"; $Connection; "SELECT * FROM wp_posts...)]
Generates an SQL query on the table [wp_posts] with restriction to the post type, which is entered in the [Post-Type] was specified (in our case "flamingo-inbound").
- Set variable [$result; Value:MBS("SQL.Execute"; $Command)]
Executes the SQL query listed above on the server.
- Loop (start)
- Set variable [$result; Value:MBS("SQL.FetchNext"; $Command)]
- Exit loop if [$result ≠ 1]
- New data record/query
- Set field value [Forms::post_date; MBS("SQL.GetFieldAsDateTime"; $command; "post_date")]
- Set field value [Forms::post_content; MBS("SQL.GetFieldAsText"; $command; "post_content")]
- Set field value [Forms::post_type; MBS("SQL.GetFieldAsText"; $command; "post_type")]
- Set field value [Forms::ID; MBS("SQL.GetFieldAsNumber"; $command; "ID")]
- Write change data/query [Without dialog box]
- Loop (end)
Execution loop in which a new data record is created and all field values are written to the created fields by the database server.
Download FileMaker sample database (.fmp12)
How to transfer scripts from the sample file to your solution
Scripts can be easily transferred from one FileMaker solution to another via the clipboard. Simply open the dialog Manage scripts, select the desired script with the mouse and copy the script to the clipboard via the menu [Edit - Copy] or key combination [Ctrl/Cmd-C]. Then open the same dialog in the target solution and simply paste the previously copied script using the menu command [Edit - Paste] again.
To transfer tables from the example file to your solution
Tables can be inserted into an existing solution in several ways. The easiest way is to import the table using the [Import...] in dialog Manage database. All tables to be imported from the source solution can then be selected in a subsequent dialog. This method is therefore particularly suitable if several tables are to be imported at the same time. If only one table is to be imported, this can also be done via the clipboard like a script. If a table including all data is to be imported, this can be done with the command [File > Import data records > File] to be carried out. In the import dialog, the target table must then be New table must be specified.
How to transfer value lists from the example file to your solution
Value lists can be easily imported from one FileMaker solution to another via the clipboard. Simply open the dialog under [File > Manage > Value lists]select the desired Value list with the mouse and copy the value list to the clipboard via menu [Edit - Copy] or key combination [Ctrl/Cmd-C]. Then open the same dialog in the target solution and paste the previously copied value list using the menu command [Edit - Paste] again.
How to transfer your own functions from the example file to your solution
Click in the dialog [File > Manage > Custom functions...] on the button [Import] and then select the sample file. Select the desired custom functions in the following dialog and confirm the dialog. Please note that this function is only available in FileMaker Pro Advanced, but not in FileMaker Pro.
Markus Schall has been developing individual databases, interfaces and business applications based on Claris FileMaker since 1994. He is a Claris partner, FMM Award winner 2011 and developer of the ERP software gFM-Business. He is also a book author and founder of the M. Schall Publishers.
