graphicpush

Thoughts on branding, design, writing and life by Kevin Potts. Established 2003.

Save zem_contact_reborn Submissions to a Database (Part 1)

This two-part tutorial will cover how to leverage zem_contact_reborn’s flexible form-building capabilities for inserting submitted data to a MySQL database, and then retrieving it on a password-protected page. Part 1 covers creating the form, the database table, and the PHP script.

The plugin zem_contact_reborn for Textpattern has been the go-to solution for website designers that need a feature-rich form mailer that goes beyond your standard Perl script. Despite its rich capabilities, it only sends submissions to a predefined e-mail address; there are no options to save the data anywhere. This is not a limitation of the plugin, just functionality that is not within its scope.

However, it is possible to leverage PHP on top of the plugin to accomplish writing submitted data to a MySQL database, and then retrieving those submissions on a separate administrative page. My goal with this two-part tutorial is to walk through the relatively simple steps, and to hopefully provide a solution to something I’ve needed here and there over the years.

Part 1 will cover setting up the form through zem_contact_reborn and attaching a database script on top of it. Part 2 (forthcoming) will cover retrieving that data on a password-protected page.

Step 1: Set Up Form With zem_contact_reborn

Download, install, and activate the zem_contact_reborn plugin.

On either a page template or inside an article, configure your form with the plugin’s tags. For this example, I’m going to use a standard “name, e-mail address, message” set of fields. However, this tutorial scales to any type of form with any type of inputs, so use whatever works for you.

Our sample Textpattern code to render the form:

 <txp:zem_contact to="you@theplace.com">
   <txp:zem_contact_text label="Name" name="name" />
   <txp:zem_contact_email label="E-mail" name="email" />
   <txp:zem_contact_textarea label="Message" name="message" />
   <txp:zem_contact_submit label="Submit" />
 </txp:zem_contact>
 

Some important things to think about as you build your form:

  • Note which fields are required, and which are not. By default, most tags are set to render their inputs as required; to turn this off, simply add the attribute required="0" to any of the zem_contact_foo tags.
  • The name attribute is optional, but this case, we need to explicitly set a name for each field. This prevents the plugin from generating a random string of characters as the ID for each input, and makes our life much easier in the next steps.

Step 2: Create New Table in MySQL

We’re going to be leveraging the database used for Textpattern to house these contact form enquiries. We’re doing this for a number1 of2 reasons3.

Log into phpMyAdmin, and select your Textpattern database from the list in the left frame. Scroll to the bottom of the page in the right frame, and create a new table. The options should look something like this:

Creating a new table on Textpattern DB

The name can be anything you’d like; I chose “enquiries” arbitrarily. For the number of fields, add up the number of input values you have in your form from step one above (in our case, it would be three), and then add two. So in our sample scenario, we would five total fields in the “enquiries” table. Once you’ve done this advanced math, click “Go”.

This will take you to a new screen where you can input the names and attributes of those fields. Here is the structure we’ll use, with the “id” field auto-incrementing so it can serve as a unique key.

FieldTypeCollationNullDefaultExtra
idTINYINTn/anononeAUTO_INCREMENT
timestampDATEn/anononenone
nameTINYTEXTutf8_unicode_cinononenone
emailTINYTEXTutf8_unicode_cinononenone
messageTEXTutf8_unicode_cinononenone

You’ll notice our two additional fields come first. The ID is important, and is used to uniquely identify each incoming message. The timestamp is more of a “nice to have”; we’ll use it to capture the date of the submission as a note of when it was received. If you’re anything like me, anything older than 24 hours is ancient history and these hints help.

Step 3: The PHP Script

How for the fun part: writing a PHP script that captures the form submissions and places them into the database. Open your text editor and save the new file as capture.php or something similar.

First, we’re going to clear any residual values.

 <?php
 // clear residual values
 $formName = '';
 $formEmail = '';
 $formMessage = '';
 ?>
 

Next, we’re going to scrub each submitted form field value with PHP’s htmlspacialchars function to help prevent injection attacks, and then link each value to a corresponding variable.

 <?php
 // clear residual values
 $formName = '';
 $formEmail = '';
 $formMessage = '';
 if( !empty( $_POST['zem_contact_submit'] ))
   {
   // scrub field values and assign to variables
   $formTimeStamp = date('Y-m-d'); // this gets the current date
   $formName = htmlspecialchars( $_POST['name'], ENT_QUOTES );
   $formEmail = htmlspecialchars( $_POST['email'], ENT_QUOTES );
   $formMessage = htmlspecialchars( $_POST['message'], ENT_QUOTES );
   }
 ?>
 

However, before we jump into updating the MySQL table, we need to set up basic validation to make sure there are no blank fields. zem_contact_reborn has great error-checking intelligence before the submission is actually sent via e-mail, but our script is processed before zem_contact_reborn, so the database would capture any submission, whether it was validated by the plugin or not, potentially leading to a huge number of MySQL records with empty or incorrect values.

For this part of the script, we are simply creating a small array for the errors. Since all three of my fields are required, all are part of the array. For your own script, do not include non-required fields in this array, because while the results might be sent by zem_contact_reborn, the database will not be updated. Also note that if this script fails, it fails silently; there is no mechanism to warn of errors that occur here. So make sure any fields you indicated were required when setting up the plugin’s tags in Step 1 correspond to this array.

 <?php
 // clear residual values
 $formName = '';
 $formEmail = '';
 $formMessage = '';
 if( !empty( $_POST['zem_contact_submit'] ))
   {
   // scrub field values and assign to variables
   $formTimeStamp = date('Y-m-d'); // this gets the current date
   $formName = htmlspecialchars( $_POST['name'], ENT_QUOTES );
   $formEmail = htmlspecialchars( $_POST['email'], ENT_QUOTES );
   $formMessage = htmlspecialchars( $_POST['message'], ENT_QUOTES );
   // start errors
   $errors = array();
   if( empty( $formName ) ) { $errors['name'] = '1'; }
   if( empty( $formEmail ) ) { $errors['email'] = '1'; }
   if( empty( $formMessage ) ) { $errors['message'] = '1'; }
   }
 ?>
 

Now we can proceed to the actual database insertion. We’re going to check if the $errors array is empty; if it is, which it should be if the user does everything right on their end, we’ll proceed to insert the data into a MySQL row.

 <?php
 // clear residual values
 $formName = '';
 $formEmail = '';
 $formMessage = '';
 if( !empty( $_POST['zem_contact_submit'] ))
   {
   // scrub field values and assign to variables
   $formTimeStamp = date('Y-m-d'); // this gets the current date
   $formName = htmlspecialchars( $_POST['name'], ENT_QUOTES );
   $formEmail = htmlspecialchars( $_POST['email'], ENT_QUOTES );
   $formMessage = htmlspecialchars( $_POST['message'], ENT_QUOTES );
   // start errors
   $errors = array();
   if( empty( $formName ) ) { $errors['name'] = '1'; }
   if( empty( $formEmail ) ) { $errors['email'] = '1'; }
   if( empty( $formMessage ) ) { $errors['message'] = '1'; }
   // start database insertion if there are no errors
   if( empty( $errors ) )
     {
       $query = "INSERT INTO enquiries SET timestamp='$formTimeStamp', name='$formName', email='$formEmail', message='$formMessage' LIMIT 1";
       $result = mysql_query($query);
     }
   }
 ?>
 

Step 4: Add the PHP File to Your Template

Upload the PHP file to your server in a folder called “inc” or something similar. Once done, add this line to the page template before everything else, including the DocType:

 <txp:php>include 'inc/capture.php';</txp:php>
 

If the contact form is inside an individual article, you may have to tell Textpattern to be more specific on the conditions for which it loads the included PHP file. For instance, if your zem_contact_reborn form is housed in an article with the id of 33:

 <txp:if_individual_article><txp:if_article_id id="33">
   <txp:php>include 'inc/capture.php';</txp:php>
 </txp:if_article_id></txp:if_individual_article>
 

You may also need to adjust the relative path to the PHP file if your contact form is not on a page off the root directory. As an example, the path cited above would work if the URL were yoursite.com/contact; if the URL is yoursite.com/about/contact, the path might need to be ../inc/capture.php. Your mileage may vary here.

Step 5: Test!

Submit a few entries through the contact form. Open phpMyAdmin, and once the “enquiries” table is selected (or whatever you called it) from the left frame, click the “Browse” tab in the upper left corner. This will show you all of the records currently in the DB. You should see your tests in there.

In part 2, we’ll cover how to retrieve these records into a password-protected web page.

Footnotes

1 From a semantic point of view, it makes sense to use this database for simple contact forms, since it is tied the Textpattern environment.

2 Some hosts only allow one MySQL database, or charge for extra databases. This alleviates that problem.

3 We’re already in the Textpattern environment, which means Textpattern has already done all of the necessary PHP calls to connect to the database (i.e., the stuff in your config.php file). From my testing, jumping into another database while inside TXP causes all kinds of rendering errors, even when closing out the database. I welcome any insight on this technical issue.

, , , , ,

commentary + criticism

Julián Landerreche

wrote the following on Friday October 22, 2010

This is something I’ve wished a few times in the past, and even asked for it at TXP forum.

For a project, I’ve done something similar by using mem_form and mem_simple_form in tandem. It would record the submission into the database, although that solution didn’t have the feature of sending the email to an email address.

Great article, Kevin. Thanks.

Ruud van Melick

wrote the following on Saturday October 23, 2010

Clearing the residual values isn’t necessary, because you only use them upon form submission and in that situation the contents of the $_POST array are used instead.

Instead of htmlspecialchars, it’s better to use the doSlash function that TXP provides.

If you use the ‘submit’ callback event, which normally used by ZCR to call anti-spam plugins, and put the code you wrote in a plugin that is loaded after any anti-spam plugins, you benefit from both the built-in error checking and the anti-spam plugins that are loaded.

MySQL can figure out the current time all by itself. If that time is to be used within TXP later on, you may want to look at how TXP itself stores dates to keep the form submission timestamps consistent with the way TXP handles dates.

Viking KARWUR

wrote the following on Sunday October 24, 2010

Thanks Kevin for the article!

Enor Anidi

wrote the following on Tuesday March 22, 2011

Hi Kevin, great tutorial…but what happened to Part 2?

andocobo

wrote the following on Tuesday January 31, 2012

Oh how i wish you’d posted part 2 of this series. I’m curious to know if you never finished it because it was too tricky or just cause you didn’t get around to it. Anyhoo, I’m planning on using this as a basis to add some functionality to a client’s website in the coming weeks – might post something about how I go.