Building your first dynamic website – Part 2: Developing the back end

XAMPP (if you have Windows)

• Learn more

MAMP (if you have Mac OS X)

• Learn more

This is Part 2 of a three-part tutorial series, in which you use Dreamweaver CS5 to build a simple database-driven website for a fictitious publication, Check Magazine. In Part 1, you set up a PHP development environment, defined a site and testing server in Dreamweaver CS5, created a MySQL database table to store blog posts, and built the forms for the content management system (CMS).

In this part, you will import data into the database table, and use PHP server behaviors in Dreamweaver to activate the forms to insert, update, and delete blog posts. Finally, you'll password-protect the CMS with a login form.

Note: Server behaviors generate PHP code automatically to provide basic server-side functionality. They should be regarded mainly as a learning tool or used for rapid prototype development.

The news table that you created in the php_test database in Part 1 of this tutorial series has four columns. If you haven't completed Part 1: Setting up the site and database, do so now, because you won't be able to continue without the basic site definition and database.

The role of each column is as follows:

• post_id: A number that acts as a unique identifier for each record—its primary key.
• title: The title of a blog post—a maximum of 150 characters.
• blog_entry: The body of a blog post—a maximum of 65,535 characters.
• updated: The date and time a record was first entered or last updated.

The database generates the values in the post_id and updated columns automatically, so the CMS is concerned only with the title and blog_entry columns.

After you have built the CMS, you'll password-protect the pages, allowing only an authorized user to add, update, and delete posts.

The news page needs a lot of content to look realistic, so I have created a data file for you to import into the news table. Building a user registration system uses the same techniques as a CMS, so to save time, you'll use another data file to import a table that contains a preregistered username and password.

1. Download check_php_pt2_start.zip, and extract the contents to a convenient location anywhere outside the Check Magazine PHP site.

   There are two files: news.sql and users.sql. If you open one of them in Dreamweaver or a text editor, you'll see they contain a series of commands written in Structured Query Language (SQL), the standard way of communicating with a database. SQL is based on natural human language, so the meaning is fairly easy to guess.

2. Launch phpMyAdmin, and select the php_test database.
3. Click the Import tab at the top of the screen.

   In the "File to import" section, click the Browse button, navigate to where you extracted the files, and select news.sql.

4. Click Go at the bottom right of the screen. You should see confirmation that the import finished successfully.
5. Click the Browse button again, select users.sql, and click Go to import it.
6. You should now have two tables listed in the php_test database: news and users. Click the link for the news table. There are 15 records with dates spanning six months. The users table has a single record with the username "adc_user" and password "dreamweaverCS5".

Note: Creating a data file from an existing database is easy. Select the database in phpMyAdmin, and click the Export tab at the top of the screen. Most times, you can accept the default options. Just click Go and save the data file.

Before applying server behaviors, you need to create a connection to the MySQL database, specifying the user account, password, and database name. Dreamweaver stores the connection details in a special file that can be accessed by any page in the site.

1. Open the Databases panel by clicking its tab, or by selecting Window > Databases. Alternatively, use the keyboard shortcut Ctrl+Shift+F10/Shift+Cmd+F10.
2. Click the plus (+) button at the top left of the Databases panel, and select MySQL Connection (see Figure 1). This opens the MySQL Connection dialog box.

3. In the MySQL Connection dialog box, use the following settings:
   • Connection name: check_mag

     The connection name not only identifies the connection, but it's also used for the filename where the connection details are stored, as well as for PHP variables. It should contain only letters, numbers, and the underscore character. There should be no spaces or special characters, and it must not begin with a number

   • MySQL server: localhost

     This is the name used by a computer to refer to itself. Occasionally, this doesn't work, so you might need to use 127.0.0.1 instead.

     If you installed MAMP on Mac OS X and decided to use the MAMP default ports, use localhost:8888 or 127.0.0.1:8888.

   • User name: phptestuser

     This is the name of the MySQL user account you want to use.

   • Password: Enter the password you chose for the phptestuser account. The sample files use Ch3!<M@g.
   • Database: php_test

   Your settings should look like Figure 2.

4. Click the Test button. If you have set everything up correctly. You should see confirmation that connection was made successfully.

   If you get an error message, check the points at the end of this section.

5. Click OK to save the MySQL connection. Dreamweaver creates a new folder called Connections in the site root. Inside the folder is a file called check_mag.php, which contains the details server behaviors need to interact with the database.
6. Look at the Databases panel. It now lists the check_mag connection.

   Expand the connection by clicking the tiny plus icon (Windows) or the triangle (Mac) to the left of the connection name. Expand Tables and then news to see the details of your table's columns (see Figure 3). The columns are listed in alphabetical order, not the order they appear in the database.

If Dreamweaver failed to connect to MySQL, check the following:

• Are your web server and MySQL running?
• Do you get an error message with a reference to "using password"? Check the spelling of your username and password. Both are case-sensitive. Check that your Caps Lock key isn't on.
• Check the spelling of the database name.
• If you get an error message saying that the testing server doesn't map to the URL for _mmServerScripts/MMHTTPDB.php, the Web URL in your testing server definition is wrong.
• Try turning off any security software temporarily, and test the connection again. If it connects, you need to adjust the security software settings to allow communication between Dreamweaver and MySQL.

It doesn't really matter which order you create the pages for a CMS, but I have chosen to start with the page that manages all the records in the database, manage_posts.php. If you activate the insert form first, you need to switch to phpMyAdmin to verify that your data was inserted successfully. This way, you can automatically redirect the browser to manage_posts.php after inserting a record to see it listed.

To display a list of records, you need to query the database to select their details. Dreamweaver calls this building a recordset.

Building a simple recordset

The Recordset server behavior's dialog box has two modes: Simple and Advanced. In this part of the tutorial series, you'll use the Recordset dialog box in Simple mode.

1. Open manage_posts.php in the Document window.
2. Open the Server Behaviors panel by clicking its tab, or by selecting Window > Server Behaviors. Alternatively, use the keyboard shortcut Ctrl+F9/Cmd+F9.
3. Click the plus (+) button at the top left of the Server Behaviors panel, and select Recordset to open the Recordset dialog box (see Figure 4).

If you see a larger dialog box, click the Simple button on the right (it's in the same position as the Advanced button in Figure 4).

Note: Sometimes, server behavior dialog boxes disappear after connecting to MySQL. If this happens, click the Document window to bring the dialog box back into focus.

4. Dreamweaver automatically inserts Recordset1 into the Name text box, but you should replace this with something more meaningful. The value is used to create PHP variables, so it cannot contain any spaces or special characters. Also, it must not begin with a number.

   Type getPosts in the Name text box.

5. Check that Connection is set to check_mag, and that Table is set to news.
6. By default, Dreamweaver selects the All radio button for Columns. This retrieves the data from every column. You should select only those columns that you actually need. This page doesn't need the data from the blog_entry column.

   Select the Selected radio button. Then Ctrl-click/Cmd-click the column names to select post_id, title, and updated.

7. The Filter options restrict the selection according to a specified condition. You want all records, so leave the Filter pop-up menu at the default None.
8. The Sort pop-up menu lists all the columns in the table. Select updated, and set the pop-up menu on the right to Descending. This sorts the records by date, with the most recent first.

   The settings in the Recordset dialog box should now look like Figure 5.

9. Click the Test button. You should see the results of the database query (see Figure 6).

Note: Don't worry about the format of the updated column. You'll learn how to format MySQL dates in Part 3 of this tutorial series.

Click OK to close the Test SQL Statement panel.

10. Click OK to close the Recordset dialog box. The Recordset should be listed in the Server Behaviors panel (see Figure 7).

11. Save manage_posts.php.

Binding the recordset results in the page

Once you have created a recordset, you need to display the results in the page. You do this through the Bindings panel, which adds dynamic data objects to the HTML of the page.

1. Open the Bindings panel by clicking its tab, or by selecting Window > Bindings. Alternatively, use the keyboard shortcut Ctrl+F10/Cmd+10.

   The getPosts recordset is also listed in the Bindings panel. Click the tiny plus icon (Windows) or triangle (Mac) to expand the recordset and reveal the column names (see Figure 8).

2. Select updated in the Bindings panel, drag it into Design view, and release the mouse button inside the first cell of the second table row. Dreamweaver inserts a dynamic text object in the cell.
3. Click inside the second cell of the second row, select title in the Bindings panel, and click the Insert button at the bottom right of the panel (see Figure 8).You should now have a dynamic text object in each table cell (see Figure 9).

As you have just seen, you can insert dynamic text objects by dragging and dropping, or by using the Insert button. The choice is yours.

4. Save manage_posts.php, and click Live View or press F12/Opt+F12 to preview the page in a browser.

   You should see the date and title of the most recent entry in the news table.

Applying a repeat region

To display more results, you need to apply a repeat region to the second table row.

1. In manage_posts.php, exit Live View if necessary, and click anywhere inside the second table row.
2. Select <tr> in the Tag selector at the bottom of the Document window to select the row in its entirety (see Figure 10).

Note: Using the Tag selector is the safest way to ensure you select the row correctly. Dragging across the table cells in Design view might not select the opening and closing <tr> tags, resulting in your page collapsing like a house of cards when you apply the Repeat Region server behavior.

3. Open the Server Behaviors panel, click the plus button at the top left, and select Repeat Region to open the Repeat Region dialog box.
4. The getPosts recordset is already selected in the Recordset pop-up menu, because it's the only one on the page.

   The Show radio buttons give you the option to show a specific number of records at a time—the default is 10—or all records. Accept the option to show 10 records, and click OK.

   Save manage_posts.php, and test it by clicking Live View or previewing in a browser. The 10 most recent records should now be displayed.

Adding a Recordset Navigation Bar

Since this page is intended to manage all records, you need a way to display the others. The Recordset Navigation Bar works in conjunction with the Repeat Region to move back and forth through an entire recordset. It automatically selects which records to display, and creates links to the next and previous sets. It gives the impression of moving through the recordset one page at a time. In fact, it's always the same page, but with different content.

1. Exit Live View, if necessary, and click immediately to the right of the table in Design view.
2. Select Insert > Data Objects > Recordset Paging > Recordset Navigation Bar. This opens the Recordset Navigation Bar dialog box, which has only two options.

   The first option specifies the recordset you want to navigate through. There's only one on the page, so it's automatically selected.

   The second option lets you choose whether to display the navigation as text or images. If you choose images, Dreamweaver inserts four green arrows in the images folder. For this tutorial, choose the Text radio button, and click OK.

   Dreamweaver inserts four links with gray tabs labeled "Show If" (see Figure 11).

The Show If tabs indicate that the links will be displayed only if certain conditions are met. For example, the First and Previous links aren't displayed if you're at the beginning of the recordset. Similarly, the Next and Last links are hidden when you reach the end of the records.

3. Save manage_posts.php, and click Live View to test the Recordset Navigation Bar. Hold down Ctrl (Windows) or Cmd (Mac), and click the Next or Last link. The last five records are displayed, together with First and Previous links (see Figure 12).

4. Exit Live View, and look at the Server Behaviors panel. It now lists 12 server behaviors that have been applied to the page. The recordset navigation bar isn't a single server behavior, but several that all work in conjunction with each other.

   Note: Server behaviors insert a lot of PHP code, often in different parts of the page. If you make a mistake, or change your mind about using a server behavior, you should always select it in the Server Behaviors panel and click the minus button at the top left of the panel to remove it. Failure to do so will leave you with a horrendous mass of corrupted code that is likely to behave erratically or not at all.

The EDIT and DELETE links still need to be fixed. You'll come back to them later.

Now that manage_posts.php displays the date and title of records stored in the news table, you'll be able to see the results of inserting new records. The next step is to apply the Insert Record server behavior to the form in add_post.php.

The Insert Record server behavior is very easy to use, particularly if you give the form elements the same names as the columns in the database table. The server behavior automatically associates input fields and columns that share the same name.

1. Make add_post.php the active document, open the Server Behaviors panel, click the plus (+) button at the top left of the panel, and select Insert Record.
2. The Insert Record dialog box should automatically select the correct values in the first two pop-up menus.

   Make sure that news is selected in the "Insert table" pop-up menu. (see Figure 13).

The Columns area indicates which form field is used to insert a value in each column. Although the post_id and updated columns are listed as getting no value, their values are generated automatically by the database.

Check that the title and blog_entry columns are being assigned the correct values. If either is marked as getting no value, it means that you have spelled the names of the form fields differently from the column names. Correct this by selecting the column name in the Columns area and selecting the form field's name from the Value pop-up menu.

3. The After inserting, go to text box at the bottom of the dialog box lets you specify which page to display after inserting a record.

   If you leave this blank, the insert form reloads ready for another post to be inserted in the database. However, it's useful to see your new record listed in the page that manages all records, so type manage_posts.php into the text box or use the Browse button to select it.

4. Click OK to add the Insert Record server behavior code, and save add_post.php.

Test the Insert Record server behavior

The page is now ready to insert records in the news table. In Dreamweaver CS5, you can do this directly from the Document window. In earlier versions of Dreamweaver, you need to launch add_posts.php in a browser.

Note: Make sure that the Preview using temporary file check box is deselected in the Preview in Browser category of Dreamweaver Preferences (select Edit > Preferences on Windows or Dreamweaver > Preferences on a Mac). A temporary file cannot be used to insert records into a database.

1. With add_post.php as the active document, click Live View or press F12/Opt+F12 to preview the page in a browser.
2. Type something into both text fields, and click the Post It! button. If you're in Live View, hold down the Ctrl/Cmd key while clicking.

   You should be redirected to manage_posts.php, and the title of the post you just inserted should be at the top of the list.

   When you exit Live View, you're returned to add_post.php in Design view.

Now that you can insert records, you need to be able to update and delete them. To do this, you need the record's primary key. The getPosts recordset you created in manage_posts.php contains the primary key of each record. You can pass the primary key to the update and delete pages by appending it to the end of the URL in a query string. A query string consists of one or more name/value pairs appended to a URL after a question mark like this:

http://localhost/check_php/admin/update_post.php?post_id=3

This passes post_id as a variable with a value of 3 to update_post.php.

You add a query string to the end of a link through the Parameters dialog box, and bind the primary key from the recordset. Although it's a simple operation, many people tend to get it wrong, so follow the next steps carefully.

1. In manage_posts.php, select the EDIT text in the third cell of the second table row.
2. Make sure the HTML button is selected in the Property inspector, and click the Browse for File icon to the right of the Link text box.
3. In the Select File dialog box, navigate to update_post.php, and select it. Don't click OK yet. Instead, click the Parameters button (it's circled in red in Figure 14).

4. In the Parameters dialog box, type post_id in the Name field. Then click in the Value field to reveal the lightning bolt icon circled in red in Figure 15. Click the lightning bolt icon to open the Dynamic Data dialog box.

Note: Make sure you click the correct lightning bolt icon. The icon also appears when the focus is in the Name field, but it's much further to the left than the one circled in Figure 15.

5. If necessary, expand Recordset (getPosts) in the Dynamic Data dialog box, and then select post_id (see Figure 16).

6. Click OK three times to close the Dynamic Data, Parameters, and Select File dialog boxes.
7. With the EDIT link still selected, open Split view to check that the code looks like this:

8. Repeat steps 1 through 7 with the DELETE text in the fourth table cell, linking this time to delete_post.php.
9. Save manage_posts.php, and click Live Code to examine the underlying HTML output. The links to update_post.php and delete_post.php should have query strings that point to each record's individual primary key, as shown on lines 18–19 and 24–25 in Figure 17. This is very important. If the links don't look like this, the update and delete scripts won't work.

The links you have just created in manage_posts.php pass the primary key of the selected record to the update and delete pages to identify the record you want to edit or remove from the database.

Creating a recordset and binding the results to the update form

In the update page, the primary key is used to create a recordset containing only the details of the selected record, which are then bound to the update form ready for editing. You also need to store the primary key in a hidden form field so that the Update Record server behavior knows which record to update.

1. Make update_post.php the active document, click the plus button at the top left of the Server Behaviors panel, and select Recordset.
2. Use the following settings in the Recordset dialog box in Simple mode:
   • Name: getPost
   • Connection: check_mag
   • Table: news
   • Columns: Select the Selected radio button, and select all columns except updated.
   • Filter: Select post_id from the pop-up menu.>

   The settings should look like Figure 18.

The Filter settings match the value of the post_id column to the URL Parameter post_id. In other words, the recordset looks for the value of post_id passed through the query string.

3. In Design view, select the Title text input field.
4. Open the Bindings panel, select title in the getPost recordset, and click the Bind button at the bottom right of the panel. This inserts a dynamic text object in the text input field.
5. Select the Post text area in Design view, and bind the value of blog_entry from the Bindings panel using the Bind button as in the preceding step.
6. Click to the right of the submit button, and insert a hidden field by clicking the Hidden Field button in the Forms category of the Insert panel/bar, or by selecting Insert > Form > Hidden Field.
7. With the hidden field selected, change its name in the Property inspector to post_id, and click the lightning bolt icon alongside the Value field. This opens the Dynamic Data dialog box.

   Select post_id from Recordset (getPost), and click OK to close the dialog box.

   This value will be passed to the Update Record server behavior to identify the record.

8. Save update_post.php, and test your code so far by switching to manage_posts.php.

   Click Live View or preview manage_posts.php in a browser, and click the EDIT link for the record you inserted earlier. Remember to hold down Ctrl (Windows ) or Cmd (Mac) when clicking the Link in Live View.

   The title and content of the post should be displayed in the update form (see Figure 19).

9. You can't edit the details until you apply the Update Record server behavior. Exit Live View and return to update_post.php.

Applying the Update Record server behavior

The Update Record server behavior is very similar to the Insert Record server behavior. The main difference is that it needs to know the primary key of the record to be updated.

1. With update_post.php the active document, click the plus button at the top left of the Server Behaviors panel, and select Update Record.
2. Dreamweaver should select all the correct values automatically in the Update Record dialog box.

   Check that the news table is selected in the "Update table" pop-up menu.

   It's also important to check the Columns area to make sure that post_id gets its value from the hidden form field, and that the Primary key checkbox is selected.

3. You want to return to the page that manages all posts after updating the record, so enter manage_posts.php in the "After updating, go to" text box.

   The settings in the Update Server behavior dialog box should look like Figure 20.

4. Click OK to apply the Update Record server behavior, and save update_post.php.
5. Test the page again by selecting an EDIT link in manage_posts.php. Make some changes to the record, and click Update Post. When you're returned to manage_posts.php, click the same EDIT link to make sure your changes were stored in the database.

   Note: A common mistake with the Update Record server behavior is forgetting to add the hidden field to store the record's primary key. If the Columns area doesn't show the primary key column as selecting the record, click Cancel, and check that you have created a hidden field in the form, named it correctly, and bound the value of the primary key from the recordset.

Applying the Delete Record server behavior

Deleting a record in MySQL is swift and irreversible. Once a record has been deleted, it's gone forever. In some circumstances, you might want to adapt the Delete Record server behavior to display a page asking the user to confirm the selection before deleting it. But for the purposes of this tutorial, you'll use the server behavior in the default way.

1. Open delete_post.php. The page never appears onscreen, so there's no need to add any content in Design view.
2. Click the plus button at the top left of the Server Behaviors panel, and select Delete Record.
3. In the Delete Record dialog box, check that the news table is selected in the Table pop-up menu.

   Dreamweaver automatically selects post_id in the "Primary key column" pop-up menu, and sets "Primary key value" to URL Parameter—in other words, the value of a variable passed through a query string.

   Dreamweaver assumes that the name of the variable is the same as the column, so it inserts post_id in the text box alongside. You need to change this value only if the name of the variable is different from the primary key column.

4. After deleting the record, you want to return to manage_posts.php, so enter the filename in the "After deleting, go to" text box.
5. Check that the settings look like Figure 21, and click OK.

6. Save delete_post.php. Test it by switching to manage_posts.php, activate Live View, hold down Ctrl (Windows) or Cmd (Mac), and click the DELETE link for the record you inserted earlier.

   The list in manage_posts.php refreshes to display the 10 most recent records. The record you entered earlier is no longer listed, because it's no longer in the database.

Only authorized writers should be allowed to add items to the Check Magazine news page, so you need to password-protect the CMS and create a login form.

Creating a login form

A login form needs just two text fields—for the username and password.

1. Create a new PHP page, and save it as login.php in the admin folder.
2. The page needs a form with two text fields and a submit button. Name the fields username and password. The form should look similar to Figure 22.

3. With the password text field selected, select the Password radio button in the Property inspector to obscure what the user types in.
4. Click the plus button at the top left of the Server Behaviors panel, and select User Authentication > Log In User to open the Log In User dialog box.

   The dialog box automatically detects the form and the username and password fields.

5. In the "Validate using connection" pop-up menu, select check_mag.

   Dreamweaver connects to MySQL to get details of the tables.

6. Use the following settings:
   • Table: users
   • Username column: username
   • Password column: password
7. You need to specify where to redirect the user on success or failure.
   • In the "If login succeeds, go to" text box, enter index.php.
   • The checkbox labeled "Go to previous URL (if it exists)" allows you to override the default success page if a user has been redirected to the login form by a password-protected page. When this checkbox is selected, successfully logging in redirects the user to the page that originally denied access.

     Select "Go to previous URL (if it exists)".

   • In the "If login fails, go to" text box, enter login.php. This redisplays the login form if the credentials don't match those in the database.
8. The final section of the dialog box has options to restrict access based on username and password, or by username, password, and access level.

   Access level is a value such as "administrator" or "member" stored in a database column. If you select the option to use an access level, you need to specify the column to use.

   The users table doesn't have a column to store access levels, so use the default option for username and password only.

   The settings in the Log In User dialog box should look like Figure 23

9. Click OK to add the server behavior, and save login.php.

Restricting access to the CMS pages

The Log In User and Restrict Access to Page server behaviors uses PHP session variables to control access to pages. Session variables preserve information connected with a specific user. Unlike cookies, the information is stored on the web server, not in the browser. The only information stored in the browser is a cookie containing a random series of characters to identify the user.

1. Open add_post.php, click the plus button at the top left of the Server Behaviors panel, and select User Authentication > Restrict Access to Page.
2. In the Restrict Access to Page dialog box, use the default option to restrict access based on username and password.

   In the "If access denied, go to" text box, enter login.php. This redirects anyone not logged in to the login page.

3. Click OK and save add_post.php.
4. Test the page by clicking Live View. Instead of seeing the insert form, you're presented with the login form.
5. Type adc_user in the Username text box and dreamweaverCS5 in the Password text box. Hold down Ctrl (Windows) or Cmd (Mac), and click the Log In button. You should be redirected to add_post.php.
6. Exit Live View.
7. The Restrict Access to Page server behavior works on individual pages, so it needs to be applied to each page that you want to protect.

   Repeat steps 1–3 with all the other pages in the admin folder, except login.php. You don't want to password-protect the login form. Otherwise, you'll have no way to access the other CMS pages.

Logging out users

PHP session variables are destroyed when the browser window or tab is closed. They should also expire after 24 minutes of inactivity, but this is not always reliable. So, it's a good idea to provide a way for users to log out. Of course, you can't rely on everyone using a logout link, either, but you should still have the option for users to sign off.

1. Open admin/index.php, position the insertion point at the end of the last menu link, and press Enter/Return to insert a new paragraph.
2. Click the plus button at the top left of the Server Behaviors dialog box, and select User Authentication > Log Out User.
3. In the Log Out User dialog box, accept the default option to create a new "Log out" link, and set "When done, go to" to login.php (Figure 25).

4. When you click OK, Dreamweaver inserts a new text link to log out the user.
5. Save index.php, and activate Live View. Hold down the modifier key and click the "Log out" link. You are taken to the login form.

   If you try to load any of the protected pages in Live View, you'll also be denied access until you log in again.

You now have a simple password-protected CMS. In Part 3 of this tutorial series (coming soon), you'll learn how to display the contents of the news table in a dynamic page. Up to now, you haven't looked at any of the PHP code generated by Dreamweaver, but in next time you'll need to dig into the code to add some conditional logic of your own.

Note: It should be emphasized that the CMS in this tutorial is very basic, and lacks many security features that would need to be added before it could be deployed on the Internet.