Building your first dynamic website – Part 1: Setting up the site and database

XAMPP (if you have Windows)

• Learn more

MAMP (if you have Mac OS X)

• Learn more

The website for a fictitious publication, Check Magazine, needs a page that can be updated regularly by your staff, without the need to write HTML or CSS. The designer has given you a piece of artwork (or comp) showing what the page should look like (Figure 1).

It's your job to create both the page and the CMS to insert, update, and delete news items in the database. The CMS needs the following files:

• index.php: Administration menu
• add_post.php: Form to insert new items into the database
  
• update_post.php: Form to update existing items
  
• delete_post.php: Script to delete items from the database
  
• manage_posts.php: Page that lists existing items for updating or deletion

In Part 1, you'll lay all the groundwork for the CMS. Part 2 shows how to activate the forms to insert, update, and delete records in the database. Part 3 brings everything together by creating the dynamic version of the news page.

When you create a normal web page with HTML and CSS, all the content is fixed by the webmaster. Everyone who visits the page sees the same content—it's static.

By contrast, the content of a dynamic web page frequently changes. For example, the news page for Check Magazine displays the two most recent articles in the database. When a new item is added to the database, the PHP code in the page automatically displays it. Click an archive link, and the content changes to display the articles for that month. The code in the page controls the content depending on the request it receives from the browser.

When the web server receives a request, it hands the page to the PHP engine, which normally runs as a module within the server. Depending on the code and type of request, the PHP engine queries the database if necessary, and then builds the HTML output to send back to the browser (see Figure 2).

It sounds like a lot of work, but the process on the web server usually takes only a fraction of a second.

You build PHP pages in Dreamweaver in the same way as ordinary web pages, and add the dynamic code using server behaviors or typing it manually. The PHP engine processes everything between <?php and ?>. Code outside those tags is treated as ordinary HTML. You can have multiple blocks of PHP code in a page, so the PHP engine constantly switches between HTML and PHP mode until it reaches the end of the document.

PHP uses variables as placeholders for values that are likely to change, and functions to perform various tasks. PHP variables always begin with a dollar sign. As a simple example, you might have a paragraph like this in a PHP page:

<p>Hello, <?php echo $name; ?>. Today's <?php echo date('l'); ?>.</p>

The PHP engine processes the code in both PHP blocks, and outputs something like this, depending on who's visiting the site and what day of the week it is:

<p>Hello, David. Today's Friday.</p>

PHP also uses loops to simplify repetitive tasks, such as displaying the results of a database query. Conditional statements make decisions using simple comparisons.

Note: You don't need to know PHP to complete this tutorial, but your ability to create database-driven sites will be extremely limited if you rely solely on Dreamweaver's server behaviors to do everything for you. There's an introductory tutorial to writing PHP scripts on the PHP website. For a more detailed discussion of PHP, see Adobe Dreamweaver CS5 with PHP: Training from the Source.

The PHP code needs to be processed (or parsed, to use the correct technical term) by a PHP-enabled web server. You also need a MySQL database and phpMyAdmin, a web-based front end for MySQL. They're free, and can be installed using an all-in-one package, such as XAMPP for Windows or MAMP for Mac OS X.

If you already have all three installed on your local computer, skip ahead to Set up your PHP site.

If you don't have a PHP-enabled web server, MySQL, and phpMyAdmin installed, refer to the following pages in Setting up a PHP development environment for Dreamweaver before continuing with this tutorial:

• Windows users: To install XAMPP on Windows, follow the instructions in Setting up PHP for Windows.
  Note: If you already have IIS installed for testing ASP or ASP.NET, use the Microsoft Web Platform Installer to install PHP in IIS. Then follow the instructions in Installing MySQL and phpMyAdmin on Windows (for IIS only).
• Mac users: To install MAMP on Mac OS X, follow the instructions in Setting up PHP for Mac OS X.

Note: Adobe does not provide support for third-party products, such as XAMPP, Microsoft Web Platform Installer, MySQL, phpMyAdmin, or MAMP, referenced in these tutorials.

Once you have a working PHP development environment, the next stage is to define a Dreamweaver site where you'll create the CMS and build the dynamic page to display the articles stored in the database. In practice, it's best to store the site files inside the web server's document root, because the server needs to process them.

The location of the web server's document root depends on your operating system and how you installed your PHP development environment.

• If you installed XAMPP on Windows, the default location of the server's document root is C:\xampp\htdocs.
• If you installed PHP in IIS on Windows, the server's document root is C:\inetpub\wwwroot.
• If you installed MAMP on Mac OS X, the server's document root is /Applications/MAMP/htdocs.

Download and extract the sample files

The files for the Check Magazine site that were used in the Creating your first website tutorial series have been modified slightly to prepare them for this tutorial.

1. Download check_php_start.zip and extract the contents to your testing server's document root.
2. Use Windows Explorer or Finder on a Mac to examine your server's document root. You should have a new folder called check_php, which contains three subfolders and two PHP files, index.php and news.php. Figure 3 shows the folder structure in XAMPP on Windows.

Defining the site in Dreamweaver CS5

Before you can start work, you need to tell Dreamweaver where to find the files and define the testing server.

Note: The site setup process changed in Dreamweaver CS5. If you want to follow this tutorial series in an earlier version of Dreamweaver, adapt the instructions for defining a PHP site and testing server in the first dynamic website tutorial written for Dreamweaver CS4, using check_php instead of Feedback.

1. In Dreamweaver CS5, choose Site > New Site. This opens the Site Setup dialog box.
2. Type Check Magazine PHP in the Site Name text box.
3. Click the Browse for folder icon alongside the Local Site Folder text box, and select check_php in your server's document root. Figure 4 shows the settings using XAMPP on Windows.

4. Select Servers from the list on the left of the Site Setup dialog box. This opens the panel where you define your remote and testing servers.

Click the plus icon at the bottom left of the panel to add a new server (see Figure 5).

5. In the panel that opens, select Local/Network from the Connect using pop-up menu (see Figure 6).

This changes the options in the panel.

6. Use the following settings:

• Server Name: Testing Server.
• Server Folder: Click the Browse for folder icon, and select the check_php folder in your server's document root.
• Web URL: http://localhost/check_php/. This tells Dreamweaver that your site is inside the server's document root in a subfolder called check_php.

http://localhost/ is the URL of your local testing server.

Note: If you are using MAMP on Mac OS X, and decided to use the MAMP default ports, change the value of Web URL to http://localhost:8888/check_php/. Adding :8888 is necessary only if you're not using the Apache and MySQL defaults.

7. Click the Advanced button at the top of the panel, and select PHP MySQL from the Server Model pop-up menu (see Figure 8).

8. Click Save. Dreamweaver lists the testing server in the Servers panel. By default, Dreamweaver selects the Remote check box.

Deselect the Remote check box, and select Testing instead (see Figure 9). This is very important.

9. Click Save. Dreamweaver closes the Site Setup dialog box, creates the site cache, and switches to the Check Magazine PHP site in the Files panel (see Figure 10).

10. Double-click index.php to open it in the Document window, and check that your testing server is working correctly by clicking Live View. The page should display as shown in Figure 11. The address in the Browser Navigation toolbar at the top of the Document window should be http://localhost/check_php/index.php.

11. Still in Live View, hold down the Ctrl key (Windows) or the Cmd key (Mac), and click NEWS in the navigation menu at the top of the page. You should see news.php, as shown in Figure 1. At the moment, the page content is just an image. By the time you reach the end of this tutorial series, it will be populated dynamically with articles drawn from a MySQL database.

Note: In earlier versions of Dreamweaver, you need to use Preview in Browser to navigate between pages.

12. Exit Live View.

Note that both web pages in the Check Magazine PHP site have .php filename extensions. This tells the web server to process the pages through the PHP engine before sending the response to the browser. At the moment, neither page contains any PHP code. The only differences from the files used in the earlier tutorial series are in the navigation menu. The FEATURES link points to index.php, and the NEWS link points to news.php. Also, the menu in news.php is now active. In the previous tutorial series, it was just an image.

You'll start working on the PHP pages in Part 2 of this tutorial series. Before that, you need to set up the database for this project.

To connect to MySQL, web pages need a username and password—in other words, a MySQL user account. A new installation of MySQL has just one user account, the superuser called root. This is the administrative account for creating, defining, and deleting databases; but it should never be used for a website. The user account for a website should have more restricted privileges, and usually has access to only one database.

When developing a database-driven website to deploy on the internet, you should normally use the same database name, username, and password in your local testing environment as allocated by your hosting company or administrator of your remote server. For this tutorial series, though, create a database called php_test and a MySQL user account called phptestuser, following the instructions in Creating a MySQL database and user account. Return to this page after creating the user account. Do not continue with the section about creating a MySQL connection in Dreamweaver.

Define the database table

A database stores data in table rows and columns in a similar way to a spreadsheet. However, you need to specify in advance what type of data will be stored in each column. It's also common to store related data in multiple tables, but this CMS stores blog posts in a single table with columns for the title, content, and date of each post. You also need a column to store a unique identifier–known as a primary key–for each record.

1. In phpMyAdmin, select the php_test database from the list on the left of the screen.
2. In the section labeled Create new table on database php_test, type news in the Name text box and enter 4 as the number of fields, as shown in Figure 12.

Note: phpMyAdmin refers to database columns as "fields". Although the words are often used interchangeably, a field is the table cell that stores one piece of information for a single record, whereas a column contains the same field for all records. So, in a table with four columns, each row (or record) has four fields.

3. Click Go to open the matrix where you define the name and data type for each column. This is so wide, you will probably need to scroll horizontally to see all the options.
4. In the first row, use the following settings:

• Type post_id in the Field text box.
• Set the Type pop-up menu to INT. This specifies the data type as an integer (whole number).
• Leave Length/Values, Default, and Collation at their defaults (blank or None).
• Select UNSIGNED from the Attributes pop-up menu. This restricts the column to positive numbers only.
• Do not select the Null checkbox. This makes entering a value in the column optional. This column is going to be used as the primary key, so a value is required for each record.
• Select PRIMARY from the Index pop-up menu. This designates the column as the primary key.
• Select the A_I checkbox. This sets the column to auto_increment, which automatically increases the number by 1 each time a record is added.

5. In the second row, use the following settings:

• Enter title in the Field text box.
• Select VARCHAR from the Type pop-up menu
• Enter 150 in the Length/Values text box. Leave all other options in the second row at their default values.
• The VARCHAR data type allows you to enter a variable number of text characters into a field. Setting Length/Values to 150 means that a title can be a maximum of 150 characters and spaces.

6. Use the following settings in the third row:

• Type blog_entry in the Field text box
• Select TEXT from the Type pop-up menu. Leave all other options at their defaults.

The TEXT data type allows you to enter 65,535 characters, more than enough for even the most long-winded blog post!

7. Use the following settings in the fourth row:

• Type updated in the Field text box.
• Select TIMESTAMP from the Type pop-up menu.
• Set Default to CURRENT_TIMESTAMP.
• Select on update CURRENT_TIMESTAMP from the Attributes pop-up menu.
• Leave all other options at their default values.

Note: On some versions of phpMyAdmin, the Attributes pop-up menu doesn't have the "on update CURRENT_TIMESTAMP" option. In this case, set the Default pop-up menu to None, and leave the Attributes pop-up menu blank.

This creates a column that stores the current date and time when a record is first created. The date and time are automatically updated when the record is changed.

8. Click Save. You should see confirmation that the news table has been created. Beneath the confirmation message, phpMyAdmin displays the structure of the table (see Figure 13).

9. Check that the structure of your table matches the settings shown in Figure 13.

To edit the table, select the check box(es) alongside the column name(s) that need changing, and click the pencil icon to reopen the table definition matrix. If you select three or less, the matrix is displayed vertically instead of horizontally, which is easier to edit.

Note: Collation determines the sort order of text columns. MySQL was originally developed in Sweden, which is why the default value is latin1_swedish_ci. English and Swedish use the same sort order. Collation is concerned solely with sort order. It has no effect on encoding.

Creating a CMS always involves at least four pages: one each for inserting, updating, and deleting records, plus another to list existing records. It's also a good idea to create a menu page, so you can navigate between the different parts of the CMS.

Create the admin menu

Since all pages except the delete script need to link to the admin menu, create it first.

1. In Dreamweaver, select the Check Magazine PHP site in the Files panel.
2. Create a new PHP page by clicking PHP in the Create New section of the Dreamweaver welcome screen.

Alternatively. select Create > New to open the New Document dialog box. Select Blank Page, set Page Type to PHP and Layout to <none>, and click Create.

3. Save the page as index.php in a new folder called admin in the Check Magazine PHP site root.
4. Change the title of the page to Admin menu.
5. The style sheet for the CMS is admin.css in the styles folder. There are several ways you can attach the style sheet. A quick one that I like to use is by selecting Attach Style Sheet from the bottom of the Class pop-up menu when the Property inspector is in HTML mode (see Figure 14).

This opens the Attach External Style Sheet dialog box.

6. Click the Browse button in the dialog box, navigate to the styles folder, select admin.css, and click OK (Windows) or Choose (Mac OS X). Then click OK to close the Attach External Style Sheet dialog box.
7. In Design view, type Admin Menu, and press Ctrl+1/Cmd+1 or select Heading 1 from the Format pop-up menu in the Property inspector to format the text as an <h1> heading.
8. Press Enter/Return to insert a new paragraph, and type Add new post.
9. Create two more paragraphs with the text Manage posts and View blog.
10. Select the text in the final paragraph, and create a link to news.php, using the Point to File or Browse for File icons alongside the Link text box in the Property inspector.The menu should now look like Figure 15.

Save index.php. You'll add the other links later.

Create the insert form

Dreamweaver has a wizard that creates an insert form and adds the Insert Record server behavior in a single operation. However, if you make a mistake or change your mind about the form layout, you need to delete everything and start again from scratch. Creating your own forms, and applying the Insert Record server behavior is more flexible.

1. Create a new blank PHP page, and save it as add_post.php in the admin folder.
2. Give the page a title, such as Add blog post, and attach the admin.css style sheet.
3. Create a heading for the page, such as What's the Buzz?, and format it as an <h1> heading.
4. Press Enter/Return to create a new paragraph. Type Admin menu, select the text, and link to index.php in the admin folder. Make sure you link to the correct file. There's also a file called index.php in the site root.
5. With your insertion point at the end of the paragraph in Design view, insert a form by clicking the Form button in the Forms category of the Insert panel/bar or use the menu option, Insert > Form > Form.
6. Dreamweaver knows that a form can't be inside a paragraph, so it displays a red outline indicating where the form will be created on the line below. The style rules in admin.css indent the form 50 pixels.

Note: Dreamweaver handles the insertion of a form and form elements differently depending on whether the focus is in Design view or Code view. These instructions assume you're working in Design view. When the focus is in Code view, Dreamweaver displays the Tag Editor, which is more complicated to use.

7. Press Enter/Return twice to create three paragraphs, and then press the keyboard up arrow twice to move the insertion point back into the first paragraph inside the form.
8. Insert a text field in the first paragraph by clicking the Text Field button in the Forms category of the Insert panel/bar, or by selecting Insert > Form > Text Field. This opens the Input Tag Accessibility Attributes dialog box.
9. Use the following settings (see Figure 16):

• ID: title
• Label: Title:

10. Leave the other options at their defaults, and click OK to insert the text field. The styles in admin.css display the label in bold on a separate line.
11. Select the text field in Design view, and type 150 in the Max chars text box in the Property inspector. This limits to 150 the number of characters that can be entered into the title text field.
12. To make the text field wide enough to enter a title, a class called textfields that sets the width property to 500px is defined in admin.css. To apply the class, select textfields from the Class pop-up menu in the Property inspector (see Figure 17).

13. Move the insertion point into the second paragraph, and insert a text area by clicking the Text Area button in the Forms category of the Insert panel/bar, or by selecting Insert > Form > Text Area.

Use the following settings in the Input Tag Accessibility Attributes dialog box:

• ID: blog_entry
• Label: Post:

Click OK to insert the text area. As before, the label appears in bold on a separate line. The styles in admin.css automatically make the text area 200 pixels high and the same width as the title text field.

14. Move the insertion point into the final paragraph, and insert a submit button by clicking Button in the Forms category of the Insert panel/bar, or by selecting Insert > Form > Button.
15. In the Input Accessibility Attributes dialog box, type insert in the ID text box. Leave the other options at their defaults, and click OK to insert the submit button in the form.
16. With the submit button still selected, type Post It! in the Value text box in the Property inspector, and press Enter/Return to change the button's label. The insert form should now look like Figure 18.

16. Save add_post.php.

Creating the update form

The update form uses the same fields as the insert form, so you can simply copy it, and make a few changes.

1. With add_post.php open in the Document window, select File > Save As (or use the keyboard shortcut Ctrl+Shift+S / Shift+Cmd+S), and save the file as update_post.php. This creates a copy of the file in a new tab.
2. Change the page title and <h1> heading to Update Post.
3. Select the submit button
4. In the Property inspector change the Button name text field to update.
5. Change Value to Update Post.
6. Save update_post.php.

Create the delete page

The delete page is never displayed onscreen, so it doesn't need any HTML added to it. Just create a blank PHP page, and save it in the admin folder as delete_post.php.

Create the page to manage records

The page that manages records in the database needs a table with two rows. The first row displays labels. The second row will be wrapped in a PHP loop to display the date and title of each record with links to the update and delete pages.

1. Create a new blank PHP page, and save it as manage_posts.php in the admin folder.
2. Attach the style sheet admin.css.
3. Change the page title to Manage Posts. Add an <h1> heading using the same title.
4. Press Enter/Return to insert a new paragraph, and create a link to the admin menu in the same way as you did in the insert form.
5. Press Enter/Return to create another paragraph, and create a link to add_post.php, using Add new post as the link text.
6. Insert a table by clicking the Table button in the Common category of the Insert panel/bar, or by selecting Insert > Table.

Use the following settings in the Table dialog box:

• Rows: 2
• Columns: 4
• Table width: 800 pixels

Leave the other fields blank, and select Top as Header (see Figure 19)

Click OK to insert the table.

7. In the first table row, type Date in the first cell and Title in the second cell. Leave the other two cells in the top row blank.
8. In the second row, leave the first two cells blank, and type EDIT and DELETE in the third and fourth cells respectively.

The page should look like Figure 20.

9. Save manage_posts.php.

Now that all the pages have been created for the CMS, link the first and second paragraphs in the admin menu (admin/index.php) to add_post.php and manage_posts.php respectively.

Your setup is now complete. In Part 2, Developing the back end, I'll show you how to activate the pages to insert, update, and delete records in the news table of your database.