12 July 2010
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
updated columns automatically, so the CMS is concerned only with the
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.
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.
In the "File to import" section, click the Browse button, navigate to where you extracted the files, and select news.sql.
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.
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
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.
This is the name of the MySQL user account you want to use.
Your settings should look like Figure 2.
If you get an error message, check the points at the end of this section.
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:
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.
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.
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.
Type getPosts in the Name text box.
Select the Selected radio button. Then Ctrl-click/Cmd-click the column names to 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.
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.
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.
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).
updatedin 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.
titlein 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.
You should see the date and title of the most recent entry in the news table.
To display more results, you need to apply a repeat region to the second table row.
<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.
getPostsrecordset 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.
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.
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.
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.
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
updated columns are listed as getting no value, their values are generated automatically by the database.
Check that the
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.
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.
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.
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:
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.
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.
getPosts) in the Dynamic Data dialog box, and then select
post_id(see Figure 16).
<td><a href="update_post.php?post_id=<?php echo $row_getPosts['post_id']; ?>">EDIT</a></td>
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.
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.
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.
getPostrecordset, and click the Bind button at the bottom right of the panel. This inserts a dynamic text object in the text input field.
blog_entryfrom the Bindings panel using the Bind button as in the preceding step.
post_id, and click the lightning bolt icon alongside the Value field. This opens the Dynamic Data dialog box.
getPost), and click OK to close the dialog box.
This value will be passed to the Update Record server behavior to identify the record.
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).
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.
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.
The settings in the Update Server behavior dialog box should look like Figure 20.
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.
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.
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.
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.
A login form needs just two text fields—for the username and password.
password. The form should look similar to Figure 22.
The dialog box automatically detects the form and the username and password fields.
Dreamweaver connects to MySQL to get details of the tables.
Select "Go to previous URL (if it exists)".
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
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.
In the "If access denied, go to" text box, enter login.php. This redirects anyone not logged in to the login page.
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.
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.
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.
Tutorials and samples