Creating a registration form

« Return to page index

The goal of the tutorial is to show how several of the 'How To' resources can be integrated together to create a customizable form connected to a MySQL database which will mail user input to the form owner and to others as defined in the setup.

The purpose

To create a form which enables users to submit information to a MySQL database - creating or updating their records each time. The information is then mailed to recipients based on input in the form.

Why

I needed to create a meeting registration form which would add or update records in a MySQL database and mail a confirmation of the registration back to the registrant as well as to the organizers. The default Mailer is fine for the latter, but some tricks needed to be employed to create a form that would mail the registrant.

 

How

First off, much of this information (if not all) is already available from How To's and Tutorials. In this tutorial I link the above references together in the following procedure:

  1. Create a Multi-Page Form
  2. Simple SQL CRUD With PloneFormGen
  3. Dynamically populate selection fields
  4. Use a Selection Field to Pick Mail Destination

 

I recommend reading those first and understand what is being done.

 

Setting up the multi-page form

This form has two pages, an initial entry form where the user adds an email, and the main data submission form.

We want entry for the users to be as simple as possible. If we have existing information for them in our database, there is no reason for them to add that information again. So the first page of the form requests an email address. Then the second page is dynamically populated with values (if they exist) from the database, otherwise the field is blank awaiting user input.

 

Create the multi-page forms

Follow the how-to instructions here.

 

 

After reading this now just:

  1. Add a the first form folder and add a string field for the email address, call it reg_email
  2. Add the second form folder and add the fields you want to be submitted to the database. 

(remember, follow all the instructions regarding the multipage forms)

 

Set up the MySQL Details

Your database should have a uid column as in the example. However, in our case we will not use this to query the database, rather we will use the email address provided. Theoretically, the email could be the primary key for the db table, but it's much easier to use an integer, and let MySQL auto_increment the value as you add entries to your database.

 

Follow the instructions here.

 

 

After reading through this, you should now set up the MySQL Methods:

  • make the testCreateRow and testUpdateRow Z SQL Methods as shown, but with arguments as you require. One of these will be reg_email
  • Now create the testReadRow method as described, but here we will make one change. Rather than using the uid, we will use reg_email and type="string". The new SQL query should look like: 

 select * from db_table where <dtml-sqltest reg_email type="string">

 

That's it for SQL Methods, now we create the Scripts:

  • The first one will take the email address which was entered on the first page of the form and query the database for values to add to the form fields:

request = container.REQUEST
form = request.form

if form.has_key('reg_email') and not form.has_key('form.submitted') :
    res = context.testReadRow().dictionaries()
    if len(res) == 1:
        row = res[0]
        for key in row.keys():
            form[key] = row[key]

 

  • The second script does the adding or updating of the database. Note: here we still use the uid as described in the tutorial (don't forget the details about setting the default value of -1 and that's its a string, even though it's an integer in your database ):

 

request = container.REQUEST
form = request.form

if int(form.get('uid', '-1')) >= 0:
    # we have a real uid, so update
    context.testUpdateRow()
else:
    context.testCreateRow()

 


Make sure you set the appropriate overides as described in the tutorial and multipage form how-to

 

 

Tricks for the email

A method to let the user choose if they want a copy of the form mailed to them as well as the recipient (you) of the default mailer.

Mailer Details

We want to add a second mailer to our folder that will send the form to the user. In order to do this we need to call on two separate tutorials:

 

First, we're going to add a selection field which will be populated dynamically with the user's email address. The catch is that we can only use selection fields for the Mailer, and the selection fields require at least two arguments. So, we'll title the selection field Receive Confirmation? and give it an id of email_conf.

 

 

In the 'overrides' panel under the Options Vocabulary add the following:

python: ((request.form['reg_email'],'Yes'),('junk@yourserver','No'))

This will create a field that asks the user if they want an email sent to them, if selected yes, can pass their email to our new mailer... which we're going to add now:

First, add a new Mailer in the form. Then, adapt the form for our purposes: directly from Steve's How-to:

Configuring the mailer

Now, edit the mail adapter for your form. (Navigate to the form folder, click on contents, find your mail adapter and follow the link; select the edit tab.)

Choose the [addressing] sub-form and find the Extract Recipient From field. You should see a None

choice and a list of all of the selection fields in your form. Select the field you just created and save your changes.

 

That's the basic gist... you should now have a form which updates a MySQL database with data entered on a form, and mails the entered data to both the form owner (you) and the user.