Creating an event registration application in PHP – Part 2: Building the back end

Dreamweaver has all the power you need to establish an online registration system in PHP, including the administrative back end. The article Creating an event registration application in PHP describes how to set up the front end, including presentation catalog and attendee registration. This article details the creation of the administrative side of the system: listing registrants, adding new ones, and updating or deleting existing attendees. In addition, you'll learn how to make it all secure with protected log-in pages. This application consists of six pages:

• Login.php – The log in page verifies the username and password of anyone attempting to access the administrative site. Once authenticated, the user is taken to the reg.php page.
• Reg.php – The initial registration page displays a table of registered attendees with links to update or delete each record. An additional link is provided to reg_insert.php.
• Reg_insert.php – This page allows the administrator to add a new registrant to the database.
• Reg_update.php – Here, the administrator can modify any existing record.
• Reg_delete.php – A page to confirm the removal of a record.
• Logout.php – This page contains code to log out the current user and return them to the log in page.

Before you begin building the application, take a moment to examine the database tables that are employed and ensure that the database connection is properly set up in Dreamweaver.

Note: It's a good idea to have your dynamic PHP site already set-up at this point and have unpacked the sample files into the local root folder.

Understanding the database schema

The database that accompanies this article is a relatively simple one with 3 tables: presentations, registrants and admin.

The first table, admin, is by far the smallest, but it is vital for any administrative application. The admin table contains the username and password (see Figure 1) for authorized administrators, which is verified at log in.

As described in this article's companion tutorial, Creating an event registration application in PHP , the presentations table maintains information for the various sessions held during the event. The table includes data columns for storing the presentation's name, a short description, and a longer description. There are also columns for the date and time of the talk, its duration and the day of the event (1, 2, 3, and so on) on which the presentation is given. Speaker details, such as name and file name for a picture, round out the table schema.

In comparison, the registrants table has far fewer data columns. Only columns for the registrant's first name, last name, e-mail address, and event name are included. You could—and probably would—require a much more robust set of data columns for an actual application, but this structure should give you a good sense of the type of information you can gather.

The SQL file for the Subscriptions database is included in the sample files download. You can recreate it on your test server through any number of utilities, including phpMyAdmin, MySQL Control Center or MySQL Administrator.

Making the database connection

After you've established your database, it's time to create a connection to it in Dreamweaver. To do so, follow these steps:

1. Choose Window > Databases.
2. Click Add (+)  and choose MySQL Connection from the menu.
3. When the MySQL Connection dialog box appears, do the following (see Figure 2):
   • Enter the name of your new connection in the Connection name field (for example, connEventReg).
   • Enter an IP address or MySQL server name in the MySQL server field. If you're working with a local development system, enter localhost.
   • Insert your user name and password in the appropriate fields.
   • Click Select to display the available databases; choose the desired one from the list.

4. Click Test to verify the connection and then OK if the connection is successful.

With your PHP dynamic site, MySQL database, and Dreamweaver connection all established, you're ready to build the first page of the application.

The log-in page is straightforward. It consists of a form with two text fields, one for the user name and the other for the password. Setting up the server-side code for this page is equally direct and requires a single server behavior.

1. Choose File > Open. Navigate to the folder with the uncompressed sample files and select login.php. Click OK.
2. From the Server Behaviors panel, click Add (+) and choose User Authentication > Log In User.

   In the sample file, the two fields are named appropriately: User Name and Password. The password field, additionally, is set to mask the entered values (see Figure 3).

3. When the Log In User dialog box opens, do the following (see Figure 4):
   • Leave the selected form in and, make sure the Username field is set to username and the Password field to password.
   • In the Validate using connection list, choose connEventReg. From the Table list, choose admin. From the Username column, choose AdminUsername and from Password column, AdminPassword.
   • In the If login succeeds, go to field, enter reg.php; in the If login fails, go to field, enter login.php.
   • Set the Restrict access based on option to Username and password and click OK.

4. Choose File > Save to store your changes.

When activated by clicking the Log In button, the Log In User server behavior verifies that the entered user name and password match the values found in the admin table. If a match is found, the initial administrative page, reg.php, is displayed; if not, the log-in page is reshown and the user name and password fields are cleared.

Note: For your convenience, a record has already been added to the admin table with the user name of admin and password of pass. Use these values when testing your sample application.

The first page the administrator sees after logging in acts as a central action center for managing the registrants table. The key element is a table that displays the registrants data at a glance. Each row represents a single record which, in addition to showing the attendee's name and number of days attending, provides links for updating or deleting the record. The page also contains a link for inserting new records.

Setting up the recordset

To set up the table of data, the first step is to establish a recordset. While populating the table is mostly a drag-and-drop affair, you'll need to do a little hand-coding to display the registered days effectively, as you'll see in the following steps.

1. Choose File > Open and, in the sample files folder, select reg.php. Click OK.

   The reg.php includes a prepared table as well as a link to insert a new registrant (see Figure 5).

Note: All of the pages in this application are based on a Dreamweaver template. If you take a look at the Template Properties for this page, you'll notice an interesting addition: an optional region called Logout. When this option is enabled, a Log Out link appears in the footer. This template property is disabled for both the log-in and log-out pages.

2. From the Bindings panel, choose Add (+) and select Recordset from the list.
3. When the Recordset dialog box appears, do the following (see Figure 6):
   • Make sure you're in Simple mode and enter rsRegistrants in the Name field.
   • From the Connection list, choose connEventReg.
   • From the Tables list, choose registrants.
   • Leave the Columns and Filter set to their default settings and, set Sort to RegLastName Ascending.

4. Click OK.

Binding data to the page

With your recordset defined, it's time to add the dynamic data to the page.

1. In the Bindings panel, expand the Recordset entry.
2. Drag the dynamic data entries to the appropriate position on the page (see Figure 7):
   • Drag RegFirstName under First Name
   • Drag RegLastName under Last Name
   • Drag RegEmail under Email
   • Drag RegDay under Days Registered

If you were to preview the page now and the first registrant had chosen to register for both days of the event, you'd see a -1 in the Days Registered column. To make that data a bit more informative, you have to do a minor bit of hand-coding.

3. Select the rsRegistrants.RegDay dynamic text and switch to Code view. Change the selected code from:

to:

I've highlighted the new code in red to make it easy to see the changes. This type of code is called a conditional or ternary statement and is basically a condensed if-then series of statements. Translated to English, this code would read, "If this RegDay value is equal to -1, then print Both, otherwise, print the value itself."

4. Save your page.

Next, you'll add the links to the action keywords in the final column of the table.

Linking to actions

To make it easy to manage individual registrant records, an Action column is incorporated. The administrator has the option to update or delete any record. As these actions are executed on their own pages, a link passing the appropriate value is required.

1. Select the text Update in the Action column.
2. From the Property inspector, click the Link folder icon.
3. When the Select File dialog box appears, locate and select reg_update.php.
4. Click Parameters.
5. In the Parameters dialog box, enter ID in the Name column and in the Value column, click the lightning bolt. In the Dynamic Data dialog box, choose RegID and click OK (see Figure 8).

6. Click OK again to close the Parameters dialog box and then again to close the Select File dialog box.
7. Select the text Delete and repeat steps 2 to 6, substituting reg_delete.php for reg_update.php.
8. Save your page.

Repeating the records

At this point, the page would display a single record. For the final phase, you'll add a Repeat Region server behavior to display all the records in the recordset.

1. Make sure your cursor is in the second row and, from the Tag Selector, choose <tr>.

   When you select the data to be repeated, you generally want to make sure to enclose everything in a <div> tag and handle the separation through CSS.

2. From the Server Behaviors panel, click Add (+) and choose Repeat Region from the list.
3. When the Repeat Region dialog box appears, make sure that rsRegistrants is displayed in the Recordset list and choose Show: All records (see Figure 9).

4. Click OK.
5. Choose File > Save.
6. To test your page, press F12 to preview in your testing server. You should see a complete listing of all the event registrants.

Note: If your registrant list grows too long for a single table, you'll want to restrict the Repeat Region server behavior to 10 or more records and include the Dreamweaver recordset navigation (Insert > Data Objects > Recordset Paging > Recordset Navigation Bar).

Although for the most part, your attendees will self-register online, you'll want to be able to add new registrants administratively for the exceptions to the rule. The reg_insert page uses the standard Dreamweaver Insert Record server behavior and, when finished, redirects to the reg.php page so you can see your results immediately.

1. Choose File > Open and, in the sample files folder, select reg_insert.php. Click OK.

   This page includes a form with three text fields (RegFirstName, RegLastName, and RegEmail) as well as a select list, RegDay, and a hidden field, RegEvent (see Figure 10). The hidden field is set to the current event, Realty Conference, and should be adjusted for any other event.

Note: It's a good idea to name your form elements the same as the corresponding data column. Dreamweaver's Insert Record and Update Record server behaviors all automatically assign such pairs dramatically reducing the workload.

2. From the Server Behaviors panel, choose Add (+) and choose Insert Record from the list.
3. When the Insert Record server behavior opens, do the following (see Figure 11):
   • Make sure form1 is chosen in the Submit values from list.
   • From the Connection list, choose connEventReg.
   • From the Insert table list, choose registrants.

   With the form fields named appropriately, Dreamweaver automatically assigns each to the proper data column, so there's no work to do in the Columns area.

   • In the After inserting, go to field, enter reg.php.

4. Click OK and save your page.

Now that you can add a new record to the database, let's make it possible to update existing ones.

Because you're modifying an existing record, setting up the reg_update.php page is a three-step process: First, you'll define a recordset that gets the desired record. Then you'll bind the dynamic data to form field elements. Finally, you'll add the Dreamweaver Update Record server behavior.

Defining the update recordset

Remember when you set the Update link to include the special parameter ID? In this step, you'll use that parameter value to filter the recordset down to just one record.

1. Choose File > Open and, in the sample files folder, select reg_update.php. Click OK.

   The reg_update.php page is quite similar to the reg_insert page (see Figure 12), with two significant differences. Instead of a select list, a text field is used for the Days field because the proper values could not both otherwise be initially displayed and inserted. To make it easier for the user, a bit of helper text is added. The second difference is the hidden field. Here, the hidden field is named RegID and will contain the ID of the selected record—a necessary step for the Update Record server behavior.

2. From the Bindings panel, choose Add (+) and select Recordset.
3. When the Recordset dialog box appears, do the following (see Figure 13):
   • Verify that you're in Simple mode and enter rsRegistrants in the Name field.
   • From the Connection list, choose connEventReg.
   • From the Tables list, choose registrants.
   • Leave the Columns and Sort options set to their default settings.
   • Set the Filter option to the following:

   RegID: =
   URL Parameter: ID

   The Filter setting gets whatever is defined in the ID variable and uses that to select the recordset.

4. Click OK.
5. Save your page.

Binding record data

Now that the recordset is defined, set the dynamic values for the various form fields.

1. From the Bindings panel, expand the Recordset entry.
2. Drag the dynamic data to their associated form elements:
   • Drag RegFirstName to the First Name text field
   • Drag RegLastName to the Last Name text field
   • Drag RegEmail to the Email text field
   • Drag RegDay to the Days text field
   • Drag RegID onto the hidden from element
3. Save your page (see Figure 14).

Adding the Update Record server behavior

Next complete the reg_update.php page by adding the Update Record server behavior.

1. From the Server Behaviors panel, choose Add (+) and choose Update Record from the list.
2. When the Update Record server behavior opens, do the following (see Figure 15):
   • Make sure form1 is chosen in the Submit values from list.
   • From the Connection list, choose connEventReg.
   • From the Update table list, choose registrants.

   Once again, Dreamweaver automatically assigns each to the proper data column because of the properly named form fields.

   • In the After inserting, go to field, enter reg.php.

3. Click OK and save your page.

Occassionally, you'll need the capacity to delete a registration from the system. The Dreamweaver Delete Record server behavior is perfect for the task. Again, you'll need to set up a recordset for the task—luckily, Dreamweaver provides a shortcut to reduce the workload.

1. Choose File > Open and, in the sample files folder, select reg_delete.php. Click OK.

   By now, this layout should seem very familiar (see Figure 16). There is, however, a notable difference: Rather than form fields, empty table cells will be used to hold the dynamic data.

As mentioned earlier, take advantage of a Dreamweaver shortcut to add the needed recordset to the page.

2. Open the reg_update.php and page and, in the Bindings panel, select the Recordset entry. Right-click (Windows) or Control-click (Macintosh) and choose Copy from the menu.
3. Switch to the reg_delete.php page and in the Bindings panel, right-click (Windows) or Control-click (Macintosh) and choose Paste.

   Because the recordset on both the Update and Delete pages is filtered by the ID parameter in the URL, you can simply copy and paste. Next, bind your data.

4. From the Bindings panel, expand the Recordset entry.
5. Drag the dynamic data to their associated form elements:
   • Drag RegFirstName in the cell next to the First Name
   • Drag RegLastName in the cell next to the Last Name
   • Drag RegEmail in the cell next to the Email
   • Drag RegID onto the hidden from element

   The dynamic text is added to make sure that the record about to be removed is the right one. Now you're ready to add the server-side code.

6. From the Server Behaviors panel, choose Add (+) and choose Delete Record.
7. In the Delete Record dialog box, do the following (see Figure 17):
   • Set the First check if variable is defined to Primary key value.
   • From the Connection list, choose connEventReg.
   • From the Update table list, choose registrants.
   • From the Primary key column, make sure RegID is selected.
   • From the Primary key value list, choose Form Variable and enter RegID in the adjacent field.

   By setting the Primary key value to the hidden form field, you're assuring that Dreamweaver will display this confirmation page. If you set it to the URL variable, the deletion would occur without confirmation.

   • In the After deleting, go to field, enter reg.php.

8. Click OK and save your page.

All of the major record manipulation is now complete. There are just two more tasks remaining, the first of which is making it possible for the administrator to log out.

When Dreamweaver logs in a user, it creates a session variable that makes it possible for anyone to use the machine at the current time to access restricted pages. A log-out page is important, because it eliminates that session variable and thus prevents any unauthorized access. Dreamweaver makes it easy to accomplish.

1. Choose File > Open and, in the sample files folder, select logout.php. Click OK.

   Although this page has a Log Out title and a bit of confirming text, in reality it will never be seen by the user. The server behavior executes when the page loads and immediately redirects the user to another page.

2. From the Server Behaviors panel, choose Add (+) and choose User Authentication > Log Out User.
3. In the Log Out User dialog box, do the following (see Figure 18):
   • Choose Log out when: Page Loads.

   Since the user has already clicked Log Out once to get to this page, you'll want to grant his or her wish immediately.

   • In the When done, go to field, enter login.php.

4. Click OK and save your page.

The final task is to protect your various pages from unauthorized access.

Now that all of your application pages are ready to go, it's time to shield them. As you'll recall, the log-in server behavior includes an option to restrict access based on user name and password. To activate that protection, you'll need to apply the Dreamweaver Restrict Access to Page server behavior to the desired pages, starting with reg.php.

1. Choose File > Open and, in the sample files folder, select reg.php. Click OK.
2. From the Server Behaviors panel, choose Add (+) and select User Authentication > Restrict Access to Page.
3. When the Restrict Access to Page dialog box appears, do the following (see Figure 19):
   • Set the Restrict based on option to Username and password.

   Dreamweaver does offer a more expansive authentication protocol that also looks at the users access level. For your purposes, the Username and Password option is good enough.

   • In the If access denied, go to field, enter login.php.

4. Click OK and save your page.

   With this page protected, you're ready to move on to the other pages in the application.

5. Repeat steps 1 to 5 with the reg_insert.php, reg_update.php, and reg_delete.php pages.

Congratulations! You've created an entire administrative application for your event registration system. To test your application, open login.php on your testing server and log in with the user name admin and password pass. From there, you'll be able to view existing registrants and update or delete their records or add new ones. Enjoy!

Where to go from here

This article covers the development of a complete administrative application for the registrants table in the database. A comparable set of pages would need to be created for any additional tables, such as presentations or admin for a complete administrative site. I leave this exercise to you to complete; you can apply the concepts in this article to building such applications.