Dreamweaver CS3 and Databases

D

R

E

A

M

W

E

A

V

E

R

C

S

3

Dre amw e av e r an d Databas e s

IL-TCE 2008

FEBRUARY 27, 2008 Sherry Ziolkowski Web Specialist Arlington Heights School District 25 ziolkowski@ahsd25.k12.il.us

telephone 847.758.3019

•

w w w. a h s d 2 5 . k 1 2 . i l . u s

•

ziolkowski@ahsd25.k12.il.us

Dreamweaver CS3 Introductions Static vs Dynamic Web pages - How this all works A static page is one that looks the same to every viewer. The client’s browser sends an http request for an HTML page from a Web server. The server sends the page back to the browser, and that’s the end of the transaction. In a dynamic page, something called an Application Server is also installed on the Web server. A browser request is sent to the Web server, and .cfm (Coldfusion) or .asp (ASP.net) or .php (PHP) language passes the request to the Application server that interacts with XML or a database to retrieve information, constructs an HTML page and sends it back to the Web server, which serves it back to the browser. Depending on the request, client viewers will see the page in different ways. Today we’re going to use Apache as our http Web server, and PHP as our Application Server. We’ll also use MySQL for our database server, and phpMyAdmin to manage the database.

Apache is the most popular web server in the world. It is free, open-source software and runs on all major operating systems. It is also built into every Mac, MySQL is a freely downloadable database server that is commonly used in web applications. SQL stands for Structured Query Language - the language that is used to communicate with the database to read, edit, update and manipulate its information. We’ll talk about: • • • • •

Databases, tables, columns, rows, and fields The MySQL server phpMyAdmin Creating tables with different field data types, and Granting privileges to users

PHP Hypertext Preprocessor was created specifically for building dynamic Web pages. It is the most popular application server language, is freely downloadable, and works well with the Apache Web server and MySQL database under both Windows and Unix.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 1

Dreamweaver CS3 Dreamweaver supports several of these different application server models (though only the MySQL database server) and generates the code necessary for the model you designate when you set up your site.

MAMP You’ll use an application called MAMP that packages all of these applications in a bundle so that you can develop and test dynamic pages on our local machine without having to set up each of the applications. MAMP stands for Macintosh Apache MySQL PHP, and it’s easy to install with a simple drag and drop process. MAMP is already installed on the computer that you are using today, but you can easily install it on your own computer: www.mamp.info. The counterpart to MAMP on a PC is WAMP - you can guess what it stands for! Information and downloads for WAMP are at www.wampserver.com.

1. In the Applications folder on the hard drive, scroll to find the MAMP folder. Open the folder to see the files it contains.

One folder has particular importance: the htdocs folder. This is the folder where you will upload all of the files for your pages today - it’s MAMP’s answer to a Web server folder.

2. Double-click on the MAMP icon to launch the program. You’ll see a console as MAMP is starting up the Apache and MySQL servers, the lights turn from red to green (hopefully) to show the servers have started, then a browser window opens with the MAMP start page. This is where you’ll manage your servers. Notice the URL of the page: http:// localhost:8888/MAMP/?language=English – 8888 is the default Apache port that MAMP uses. It’s important to mention, because when you are testing your pages, you’ll need to include :8888 in the URL after localhost.

3. Also notice that the user and password names for MySQL are both root. Because you’re testing on a local machine, it’s not crucial to change those default passwords, but they definitely need to be changed on a Web server for security.

Define Your Site 1. Drag the directory folder to Applications > MAMP > htdocs folder from the DWDatabaseICE CD. 2. Start DW CS3 and choose Site > New Site. Select the Basic tab in the window.

3. Give the site a new name: Type staff directory in the first field and http://localhost:8888/directory in the second field 4. Click Next. The next screen lets you choose whether you’re building a static or dynamic website.

5. Select “Yes, I want to use a server technology,” then choose PHP MySQL from the pull down menu. Click Next.

6. Select “Edit and test locally” since your Web server is on your computer. The second option is the one you’ll most likely choose when setting up a real site that you’ll FTP to a real Web server.

7. Tell Dreamweaver where to find your files. Click on the folder icon and browse to Applications > MAMP > htdocs > directory. Click Choose. Click next.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 2

Dreamweaver CS3 8. Type http://localhost:8888/directory/ in the URL field. DO NOT forget the ending /. Click Test URL. If you get an error message here, you’ve either typed the URL incorrectly, you forgot the ending slash, or the Web server isn’t running.

9. Click Next. Choose No for using a remote server. Click Next.

10. Click Done. You’ve successfully set up DW for a dynamic site and you’ll see your files in the Files panel.

Databases: a Quick Introduction You’ve set up an application server and a database server, and you’ve defined your site in DW. You’re ready to set up two new databases in MySQL. Quite simply, a database stores information. Databases contain the electronic equivalent to file folders: tables. A table is a container that holds information about a set of similar items: in our case, a staff directory. Our staff table contains a name, email address, voice mail, building and assignment. Each type of information is stored in a column; for example, the email Primary Key

staffID

Last_Nam

First_Na

Building

Email

VM

Position

Assignment

1

eAbruscato

me Nancy

Westgate

abruscato@ahsd25.k12.il.us

7446

Teacher Elementary

Kindergarten

2

Ackermann

Eileen

Patton

ackermann@ahsd25.k12.il.us

4883

Clerical Asst

Clerical

3

Adams

Kristine

Greenbrier

adams@ahsd25.k12.il.us

7548

Teacher Elementary

4th Grade

4

Ague

Rebecca

Ivy Hill

ague@ahsd25.k12.il.us

7619

Social Worker

Social Worker

5

Aho

Matt

Thomas

aho@ahsd25.k12.il.us

7219

Teacher Middle Sch.

Science

addresses for all staff are contained in one column. All information pertaining to a

Primary Key

Foreign Key

single staff member, across a row, is called a

userID

user_name

password

staffID

access_level

record. You can create a separate table

1

stevansher

********

572

1

containing information for a log-in system with

2

kadams

********

3

3

3

ilikesheep

********

22

2

4

kinderkid

********

234

3

passwords and usernames, then tie the two tables together. This is called a relational database. To connect information between

tables, you create a relationship between them. The most common way to connect tables is by using a primary key – a unique identifier for each record in the table. Database tables can be set up to automatically assign an ID to a record each time a new one is added to the table.

This primary key is then designated as a foreign key in the second table, creating a relationship. The second table also contains it’s own primary key.

To learn more about databases and how they work, check the resources at the end of this tutorial. Creating relational databases is tricky and takes a lot of pre-planning. If your situation is like mine, the spreadsheet had already been created and it was up to me to make it work on the Web! Directions for converting an Excel file to import into a MySQL database can be found on the class CD. I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 3

Dreamweaver CS3 phpMyAdmin and MySQL The next steps are to go to the phpMyAdmin database administration tool and create a new database, load the data into the new database, and create a new user for accessing and updating the database.

1. Click on the phpMyAdmin button at the top of the MAMP start page. (If the MAMP start page isn’t open, click ‘Open start page’ on the MAMP console.)

2. In the ‘Create new database’ box, type newsletter and click Create.

3. Name the table visitors and indicate the number of fields as 6. Click Go.

4. Fill in the columns as shown above: you’ll set up the first field as a primary key that you’ll have MySQL automatically add a number to each time a record is added. The Field name is visitorID, and the type is an INT – an integer. Enter 8 as the length - you probably won’t get more than 99 million visitors to sign up for your newsletter! Choose UNSIGNED as the Attribute: this means that the column is limited to positive numbers or 0. The Null attribute defaults to not null. Null is the same as saying the field has no value, so it means that the field is required to have data in it.

5. Leave Collation blank in all fields: MySQL will fill it in with the default collation of ‘latin1_swedish_ci’. The swedish language is based on latin (as is evidenced by the name latin_swedish) and uses all of our standard characters as well as a few extras.

6. Fill in the next four columns as shown setting the type as VARCHAR and the Length as 32. This means that the field can have a varying amount of characters and that there can be no more than 32 of them. You would use CHAR if you know there will always be a certain number of characters, like for a zip code or state abbreviation.

7. Finally, the last field is a date field. Give it the name of date and choose date as the Type. MySQL uses the date format of YYYY-MM-DD. 8. Click Save. You should see a table that looks like the figure above.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 4

Dreamweaver CS3 Adding Privileges (Users) to phpMyAdmin

1. Click the ‘localhost’ link at the top (or click on the house icon in the sidebar).

You’ll return to the main phpMyAdmin page to add privileges to this database.

2. In the left column of links in the middle, click on the Privileges link. This step opens the User page. From here you can edit current users (ie: change a password) and add new users.

3. Click the “Add a new User” link.

4. Type staffAdmin for the user name, and Local: localhost for the Host. In the password field type admin, and admin again in the re-type field. This user can only access the database locally from the server. This means someone out on the Internet can’t try to log in to the MySQL server using this name and password only local access, like PHP pages being run on the same computer, is allowed. If this were on a Web server, I would recommend a much more difficult password!

5. In the Database for user area, leave as None. In the Global privileges area, click ‘Check All’. You are creating a super user that will have access to all databases and all functions within phpMyAdmin. Click Go at the bottom of the page.

Another page opens where you can edit the user privileges. (If you were to add additional users, turn on all checkboxes in the first Data column to bestow only data privileges to that user for this database. That user will NOT be able to alter the database structure in any way.)

6. Click Go. 7. There’s just one final step: you must “flush” the database privileges to activate the new user and its privileges. In the sidebar, click on the Home icon to return to the main phpMyAdmin page. Click on the Reload privileges link in the first column.

8. You can remove Users by clicking on Privileges again, checking the box next to the user you want to delete and clicking Go in the Remove selected users box.

Creating the Staff Database You’ve gotten familiar with how to create a database in phpMyAdmin. Now you’ll create a staff database and add the sql data that’s been provided for you.

1. Open the main phpMyAdmin page.

2. In the ‘Create new database’ box, type staffdirectory and click Create. Now you’ll need to add data to the database – In the top navigation bar, click ‘Import’.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 5

Dreamweaver CS3 3. This page allows you to choose the file you wish to import. Click the Choose File button and browse to Applications > MAMP > htdocs > directory > staffdirectory.sql. Click choose. Leave all other settings as is, and click Go. 4. Hopefully, you’ll see that the import has been successful! If not, you’ll see an error message that may give you a clue as to what is wrong with the data that you tried to import. You can open the .sql file in TextWrangler and try to fix the error. Also this shouldn’t take a super long time - if you’re waiting and waiting there is probably a problem. Stop your browser, go back and drop the database you just created and try again.

5. You’ll see in the left sidebar that three tables have been added to your database.

6. Click the ‘localhost’ link at the top (or click on the house icon in the sidebar). You’ll return to the main phpMyAdmin page to add privileges to this specific database.

7. Click on the Privileges link.

8. Click the “Add a new User” link.

9. Type staff for the user name, and Local: localhost for the Host. In the password field type staff, and staff again in the re-type field. Again, this is on our local machine - you may as well use an easy to remember user name and password.

10. Leave all settings at the default, and Click Go at the bottom of the page.

Another page opens where you can determine the user privileges. In MySQL, you can limit which users have access to which databases, and how much power they have to work with the databases they have access to. For example, you can give a user the ability to read, update, and add data to a database, but not add or delete tables in the database.

11. At this point, the user you just created doesn’t have any privileges. Scroll down the page to Database-specific privileges, and select directory from the pull-down list.

12. Yet another page opens where you can specify what the user can and can’t do to the database.

13. Turn on all checkboxes in the first Data column, to bestow only data

privileges to that user for this database. That user will NOT be able to alter the database structure in any way. It’s best to limit the access to the minimum that each user needs. Click Go. 14. On the main localhost page, click on the Reload privileges link in the first column to flush the privileges again.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 6

Dreamweaver CS3 Creating a Dynamic Page You already know how to handle the first step: Design an HTML page to display the database information. However, your page will not end with the .html suffix - it needs to end in the .php suffix for dynamic pages. (or .asp (ASP), .aspx (.Net), .cfm (ColdFusion). The file extension you use is important: a Web server uses it to identify the type of page requested.

The process for creating a new blank dynamic page is the same: just choose PHP instead of HTML. When you save the page, DW will add the .php extension. You can now design the page using CSS, Spry widgets, tables or whatever just as you would design an HTML page. Your PHP page will still contain lots of HTML code.

The pages for you to work with have already been designed to save some time.

1. Back in DW, select index.php from the Files panel and double-click to open it.

2. Before you can add dynamic content to a page, you must create a connection to a database. Open the Databases tab in the Applications panel. (Window menu > Databases) 3. Click the + sign. From the pull-down menu, choose MySQL Connection.

4. The connection dialog box opens. For Connection name, type connDirectory. The ‘conn’ reminds you that it’s the name of a connection. In the MySQL server box, type localhost to tell DW where to look for the database – in this case, our local machine.

When you are ready to upload this to a server, it’s possible that the connection settings will need to be changed depending on how the MySQL server is set up on the Web server. Next, type the user name and password for the database: staffAdmin and admin. Finally, click the Select button to locate the database. This dialog box lists all the databases available to you. You will see 5 of them: information_schema, mysql, test, newsletter and staffdirectory - the first 3 were installed in MAMP, and the last 2 are the one you created and the one you imported. Click on ‘directory’ and click OK. Now click on the Test button. DW will tell you that your connection was made successfully! Click OK then click OK again to close the Connection dialog box.

5. In the Database panel, you’ll now see your ‘connDirectory’ connection. Click on the triangle to expand it, then click on the triangle next to Tables, and you’ll see the names of the tables that are now in the database. Click on one of the triangles, and you’ll see the columns contained in that table.

6. Save. Often!

You’ve laid the foundation for your dynamic website. Now you’ll start adding data from the database to your pages.

Adding Dynamic Data What makes dynamic pages different from static pages is the ability to selectively retrieve information and to choose how you want the data displayed. To retrieve specific data to place on a page, you start by creating what is called a Recordset.

It is a command, or query, to the database asking for particular information. “Hey database, show me a list of staff names!”

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 7

Dreamweaver CS3 1. Open the Bindings panel. The bindings panel is the control center for retrieving information from a database. Click the + button and choose Recordset (Query). The Recordset dialog box opens. Make sure you’re in Simple mode - you are if you see an Advanced button on the right - if you see a Simple button, click it.

2. Type rsStaff into the Name box. Beginning a recordset with ‘rs’ reminds you that its a recordset. From the Connections pull-down, select ‘connDirectory’ – the database you just connected to.

3. From the Tables menu, choose ‘staff’, then click the Selected radio button. Since you’re just displaying a staff list here that a user can click on to see more detailed information, you just want to retrieve a few fields.

4. In the columns list, command-click (to select more than 1): ‘first_name’, ‘last_name’, and ‘assignment’. You don’t need to filter this recordset, so you can ignore that area.

5. To sort the data, choose last_name from the Sort pull-down menu. Leaving Ascending chosen with display the list from A to Z. 6. Click the ‘Test’ button on the right. You should see a list of the first 25 records in the database. Click OK, then click OK again to ‘bind’ the data to the page.

Displaying the Data 1. In the bindings panel, click the triangle to open the recordset if it’s not already expanded.

2. In index.php, click inside the table cell below First Name. Click on ‘first_name’ in the Bindings panel, then click ‘Insert’. This is one way to insert dynamic data on the page. You’ll see this code: {rsStaff.first_name}.

3. For the Last Name and Assignment, simply drag the corresponding data bindings into the appropriate cells. That’s an easy way to add data to a page! Save.

4. Click the Live Data View button next to the (Design view button). You’ll see that one record - the first one - appears. But where are the rest? Click the Live Data view button again to turn it off.

Insert Bar > Data Tab

5. You need to add a Repeated Region to your data so that all of it (or as much of it as you want) appears. Click in one of the cells that contains dynamic data. Now click on the <tr> tag in the Tag Selector at the bottom of the document window.

This is the easiest way to select an entire row.

6. In the Insert bar, Data tab, click on the Repeat Region icon (see above). Change Show 10 records at a time to 25. Since there are over 600 records, they don’t all need to be on the same page!

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 8

Dreamweaver CS3 7. Click on Live Data view again - now what do you see? Turn off Live Data view when you’re done. Save.

8. There’s one more problem - you need a way for your viewer to navigate to the rest of the records. DW has an easy way to do that, too.

9. Click anywhere in the table, then click the <table.names> tag in the tag selector to select the table. Now press the right arrow key once to move your cursor to the right of the table. In the Data Insert bar, click on the Recordset Paging button and select Recordset Navigation Bar from the menu.

10. Make sure the text button is selected. Click OK. Now you’ll add one more feature to your page: a record count display.

11. Click at the end of the headline “Staff Directory” and then press Return to make a new paragraph. Click on Record Count in the Insert bar, and choose Recordset Navigation Status from the menu. Click OK in the dialog box.

12. Save your page and preview it in a browser. Cool, huh?

Editing a Recordset and Linking to a Detail Page Now that the main staff list is complete, you need to to create a link to the name of each staff member that opens a page of details for them. You must add parameters to the staff names that will tell the browser which detail to display on the details page - the primary key: staffID. DW doesn’t provide a simple, one-click way to do this. There are several steps involved.

1. Open the Server Behaviors panel. It contains all of the behaviors you’ve added to the page so far. Each staff member has their own ID number, so you’ll use that data to direct viewers to a detail page.

2. Double-click Recordset (rsStaff) in the Server Behaviors panel to open the Recordset dialog box again. Command-click ‘staffID’ in the column to add it to the list. Click OK. 3. A simple link to the detail page will not work here. Since the page containing the staff member’s details is dynamic, you’ll need to add a server behavior.

4. Select the dynamic data for First Name. Click the file folder icon in the Property Inspector- the same process as making a regular link. Locate the ‘detail.php’ page in the directory folder... but don’t click Choose yet.

Instead, click on the Parameters button next to the URL field.

5. In the dialog box, type staffID in the name column and press the tab key to open the Value field. Click on the dynamic data button (the one with the lightning bolt), and select staffID from the list. Click OK to close the Dynamic Data box, OK to close the Parameters box, and Choose to accept the dynamic link.

6. Repeat the process for the Last Name dynamic data so the viewer can go to the details page no matter whether they click the first or last name.

7. Save and preview in a browser. Click on a staff name – the detail page loads without any details! In the meantime, look at the URL in the browser’s address bar. It should look like this: http://localhost/directory/ directorydetail.php?staffID=1. The ID tagged at the end shows the ID of the staff member whose record you clicked. That is what is needed to bring up the proper staff member’s detail on the detail page.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 9

Dreamweaver CS3 Building the Detail Page Now you’ll build the detail page to display staff member data, and you’ll also create an advanced recordset that combines information from two separate database tables.

1. Open the ‘detail.php’ page.

2. There is already a connection to a database - that connection is for the site and remains for all pages.

3. You need to create a recordset for this page, however. Click the Insert Recordset icon on the Data insert bar (or click the + sign in the Bindings panel again).

4. This time, you need to be in Advanced mode, so click the Advanced button on the right. In the Name field, type rsDetails. From the Connection menu choose ‘connDirectory’.

5. Click the triangle next to Tables in the Database Items list at the bottom of the dialog box, then expand the staff table.

6. Now select the fields that will appear on the detail page: click ‘staffID’ and click the Select button on the right. Notice that DW writes SELECT staff.staffID FROM staff in the SQL box. This is SQL code for selecting data from a particular column in a table.

7. Repeat step 6 for last_name, first_name, email, voice_mail, position and assignment. These are all items to be displayed on the detail page.

8. Click the triangle to expand the buildings table. Repeat step 6 for buildingName.

9. Click the test button. Right now, the query structure retrieves all of the records for both tables, so each staff member is listed about 10 times! You need to combine the information from both tables, and retrieve only the information for the particular staff member ID. Click OK to close the test box.

10. Click inside the SQL box after the word ‘buildings’ and press return.

11. Type WHERE staff.buildingID = buildings.buildingID This bit of code is called a join statement because it joins two tables together. When you retrieve directory information for a staff member, you also want to retrieve the building they work in.

12. Click the + button next to the Variables area. The Edit Variable dialog box opens.

13. Type varStaff in the Name field. Leave the Type field as Numeric, and enter the number 1 in the Default value field. Type $_GET[‘staffID’] as the Runtime value.

When you add a variable to a SQL query, you’re telling that query to respond to information from another source: in this case, the unique ID number that is passed from the links on the Staff Directory index page.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 10

Dreamweaver CS3 14. Click OK to add the new variable. Now click after buildings.buildingID and type AND staff.staffID=varStaff.

15. Click the Test button to see if the SQL query worked. If you get an error message, check your syntax. Make sure that you typed periods and not commas, and that you don’t have extra spaces. Click OK and OK again. You can find the correct code in the text_files folder on your CD. Filling in the Details 1. Now all you need to do is add the information from the recordset to the page. On the details page, highlight ‘Staff Member’ in the title. Click on first_name in the Bindings panel and then click Insert. Highlight the word ‘Info’ and insert last_name.

2. On the next line, type Email: and drag (or insert) the email data. Click after the email code, add a space, then type a comma. Type Voice Mail: and add the voice_mail data. Click after the voicemail data.

3. Press return for the next line. Add assignment data, type a comma and a space, and add the position data. Press return.

4. Add building data on the next line. Dynamic information can be styled just as you would any other text.

5. Save the detail page, and preview the index page in a browser.

Making a Category List Now you’ll make a building list in the sidebar that will display only the staff members in that building when clicked.

1. You’ll add the category list to this detail.php page, but tou could be add it to the other pages as well.

2. Add an additional new Recordset from the Bindings panel. Make sure you are in Simple mode.

3. Type rsBuildings in the Name field and select ‘connDirectory’.

4. Select buildings from the Table menu and make sure the All button is selected.

5. Leave Filter alone, and Sort by buildingID, Ascending so that the schools will appear in the list first. (That’s how they are set up in the database.)

6. Click OK to apply the recordset to the page.

7. Sometimes it’s hard to see where to put a cursor on a styled page, so go to the View menu > Style Rendering > Display Styles. This will turn off the styles on the page to reveal the HTML. 8. Scroll down the page and locate the empty bullet that appears after ‘District Buildings’. Drag buildingName from the reBuildings recordset next to the bullet from the Bindings panel.

9. In the tag selector, click on the <li> tag. The tag selector is the best way to make sure you are selecting an object including the HTML tags around it.

10. In the Data tab of the Insert bar, select Repeat Region. In the dialog box, select rsBuildings from the menu. Click the All radio button, and click the OK button to create the repeat region.

11. Now add the dynamic link to make the list functional: Click on the ‘{rsBuildings.buildingName}’ text to re-select it.

Click on the folder icon next to the link box.

12. Locate the building.php file in the directory folder.

13. Click the Parameters button and type buildingID Name column and then press the tab key to move to Values. Click the Dynamic Value button (lightning bolt). Expand the rsBuildings recordset and choose ‘buildingID’ from the list.

14. Click OK. Click OK again to close the Parameters window, and finally click Choose to finish the link.

15. Go back to View > Style Rendering > Display Styles to turn the styles back on.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 11

Dreamweaver CS3 16. Save and preview in a browser. Click on a building in the menu to see how it works. The page is blank, but notice the ID in the URL address.

Build the building staff list page At this point, you know the drill – you have to define a recordset, and add the dynamic fields to the page... you’ll add a few twists, though.

1. Open building .php and add a Recordset.

Go to Advanced mode.

2. Name it rsBuildingDetail and choose ‘connDirectory’.

3. In the Database items area, expand the buildings table, click and Select: buildingID, buildingName, address, zip, phone and image.

4. From the staff table click and Select: last_name, first_name, buildingID, and assignment.

5. Click the + sign to add a variable. Name it varBuilding, default value = 1, and type $_GET[‘buildingID’] as the Runtime value.

You did this before for the staff detail page when you were passing the staffID link through a URL - this time you’re passing the buildingID through a link. Click OK.

SELECT buildings.buildingID, buildings.buildingName, buildings.address, buildings.zip, buildings.phone, buildings.image, staff.last_name, staff.first_name, staff.buildingID, staff.assignment FROM buildings, staff WHERE staff.buildingID=buildings.buildingID AND buildings.buildingID=varBuilding ORDER BY staff.last_name

6. Now you need to join the two tables, buildings and staff, and enter the variable into the SQL statement: Type WHERE staff.buildingID=buildings.buildingID AND buildings.buildingID=varBuilding on a new line after the FROM buildings, staff statement.

7. Finally, you need to add an ORDER BY statement so that the data is displayed in order of last _name rather than the primary key (staffID) default. Click on last_name in the staff table and click ORDER BY. 8. Your final SQL statement should look like the text above. Click the Test button to make sure there are no errors. If you get errors, you can copy and paste the text from the buildingpageSQL.rtf file in the text_files folder on your CD. 9. Click OK. 10. Save the page.

11. Add the dynamic data to the page: Replace the word ‘Building’ in the Header with the ‘buildingName’ dynamic data.

Add ‘address’ , ‘zip’ to the line below the headline. Press return. Add ‘phone’ to the next line. Add ‘first_name’, ‘last_name’ and ‘assignment’ to the appropriate table cells.

12. Select the dynamic data row in the table, and add a repeat region. Show15 at a time.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 12

Dreamweaver CS3 13. Add a Recordset Paging Navigation bar below the table. Save.

14. Finally, you’ll add an image to the page. The actual images are not in the database - just the url of the image - so you’ll need to add a dynamic source for the link. Double-click the words ‘building image’ to highlight them under the banner graphic on the right side of the page. Delete that text, but leave the cursor where it is. Go to Insert > Image.

15. In the dialog box that opens, you’re not going to navigate to an image like you normally would. Click the Data Sources button next to ‘Select file name from:’ at the bottom of the panel.

16. In the Dynamic Data dialog box, click on ‘image’. You need to point to the folder that the image resides in, so type images/ prior to the php echo statement in the Code field. Click OK. 17. If the alt text dialog box appears, type building image for the alt text.

18. Click OK. Save and preview the page.

You have just built three powerful, dynamic and complex Web pages – but these pages only display data. Next you’ll create a form to collect data from your visitors.

Creating Dynamic Forms The primary method of collecting information over the Internet is the HTML form. Dreamweaver has Record Insertion Form Wizard that makes creating a form easy. Now you’ll make a page to collect data from viewers who want to subscribe to your newsletter.

1. Open newsletter.php from your site Files panel.

2. Since the newsletter data will be posted to the newsletter database that you created earlier, you’ll have to make a new database connection called connNewsletter from the Databases panel. (See left.)

3. Click in the space below the Newsletter Signup title. Go to Insert > Data Objects > Insert Record > Record Insertion Form Wizard. (You can also find the Form Wizard in the Insert Bar > Data tab > Insert Record icon.)

connNewsletter

4. From the Connection menu, select your newsletter database connection, so DW will know where to put the data you are collecting.

5. From the table menu, choose the visitors table.

DW only lets you choose one table to insert data into per form. However, you can put multiple forms on a page.

6. After inserting, go to: > browse to thankyou.php. This is the page visitors will see after they have submitted their information.

7. In the Form fields box, select the database columns you wish to include in the form. The first thing you need to do is remove the visitor I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 13

Dreamweaver CS3 ID column. This is a column that MySQL automatically fills in, so you don’t want your visitors to mess with this column. Click on the - sign to delete it.

8. Now first_name is highlighted in the column: Change the Label to First Name: This is what appears next to the entry field on the form. Display as and Submit as default to text field and text. This will be fine for this field.

9. Change the last_name label to Last Name: 10. Email is okay as is.

11. Click on the building column. The label is fine, but you want to display the buildings in a pull-down menu. Click on the menu next to Display as and choose Menu. Submit as Text is okay.

12. Click on the Menu Properties button. You’ll build the menu manually.

There are 9 schools in our district - let’s just enter three: Dryden, Olive and Thomas. Click the + sign three times to get four items. You need four items because item1 should be informational. Label it Choose a school, and leave ‘Value:’ blank. Label the next three items with the school names -Dryden, Olive and Thomas - and put the respective school names in the value field. Click OK. 13. Edit the date column. The default label, display and submit are fine, but you need to add some php code in the value field to display today’s date so the viewer doesn’t have to type it in. Type <?php echo date("Y-m-d")?> in the Default value field.

14. Click OK. Your form will appear in a table on your page. Change the ‘Insert record’ text on the button to ‘Signup’ in the Properties Inspector.

15. It’s a good idea to add some authentication to your page to make sure that there are no duplicate records. Go to the Server Behavior panel and click the + button. Choose User Authentication > Check New Username. Choose email in the Username field to check for duplicate email addresses. Browse to duplicate.php telling the user that their email address is already on file. Click OK. 16. Save and preview your page. Notice that the date begins with the year. Not ideal, but that’s the format MySQL uses for date fields. Enter something in the fields and press the Submit button. You’ll see the thank you page.

17. Let’s open the database to see if any data was entered. Open the MAMP console and click ‘Open start page’. Click the phpMyAdmin button at the top. Choose your database in the left sidebar, and then click on the visitors table to open it.

Now click Browse to see the data in that table. You should see the data that you just entered!

18. Test the page again and try to enter the same information. Can you?

Validating Forms with Spry Validating the form is good idea to ensure that you get complete data.

DW CS3 has new Spry validation widgets that allow you to display friendly “Hey, you forgot a field, dummy!” messages on your page. You can specify that a field may not be left blank, or that it contain information in a certain format. If someone tries to submit the form without the proper information, they’ll be notified directly on the page itself.

1. Click on the first field, and click the Forms tab on the Insert bar. The Spry validating widgets are on the right side of the bar. Choose the Text Field Validation icon.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 14

Dreamweaver CS3 2. Dreamweaver adds span tags around the field, a label and additional HTML for displaying error messages. In addition, when you save the page, DW will add Javascript files at the root of your site and CSS styles that display your messages correctly.

3. You can customize the widget by using the Property Inspector. Rename the widget spryfirst_name. Leave ‘Type’ blank because it’s just text. In the Preview states pull-down menu, choose ‘Required’. Notice that red text appears next to the name field stating “A value is required”. That’s the error message that will appear if the viewer clicks the Submit button without putting something in the field. You can edit that text to say anything you’d like. Each widget has different states. If you choose Initial or Valid in the Preview states pull-down, notice that the error message goes away.

You can edit what your form will look like for each of these states.

4. By the way, you can edit the css file that is saved for the widget, but that’s for a different class!

5. Click the ‘blur’ box in the ‘Validate on’ area. Blur means that the field will validate when the viewer presses tab to move to another field or when he clicks elsewhere on the page. The Change option means that validation occurs whenever a change happens - and a change occurs every time a character is typed. This could get a little annoying - so be careful with this option in a text field or an email field, for instance. The change option is a good idea when validating a pull-down field, radio buttons, or checkboxes.

6. For this text field, leave all other options as is. Repeat for the Last Name field, naming it sprylast_name.

7. Choose the text field validation widget for the Email field, too. Name it spryemail. This time, choose ‘Email address’ as the Type. You’ll see that there is an additional state: ‘Invalid Format’. If the viewer doesn’t enter an @ symbol, the field will be determined to be invalid. It doesn’t, however, validate whether the email address itself is valid or not.

8. For the Building field, you get to choose a different widget! In the Forms tab, choose the Spry Validation Select widget (last one on the right). Name it sprybuilding. Check ‘Do not allow Blank value’ and make the Preview state ‘Required’. For the menu, choose ‘Validate on Change’.

9. Date is also a text field, but you entered the PHP code to generate the date. You don’t have to make this field required because most people will realize that the date is okay and skip over it – but they could erase what’s in the field and type over it. Add a text field validation widget to the Date field. Name it sprydate. Choose ‘Date’ for the Type and ‘Initial’ for the Preview state. In the Format pull-down, choose yyyy-mm-dd. That is the date format that is required for the MySQL database.

10. Save and preview. Try to move among the fields without entering anything. Your viewers are not getting anywhere unless they fill in the fields!

The Insert Record Wizard is great, but you may want to create your own custom forms. You also don’t want just anyone to be able to get into your records and edit them. You’ll learn about the Insert Record behavior next: you’ll build an admin area to manage your database.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 15

Dreamweaver CS3 Using the Insert Record Behavior 1. In the admin folder in your Files panel, open add.php.

A custom form has already been created for you.

2. Go to the bindings panel and create a Recordset called rsStaff, using connDirectory and the staff table, and selecting All columns. You don’t need to sort.

3. Create another Recordset for the buildings table called rsBuildings. Select the buildingName and buildingID columns and sort by buildingID, Ascending.

4. Click in the first field of the table. Open the Server Behaviors panel. You’ll see the two recordsets that you just created. Click away from then to deselect them.

5. Click the + sign and choose Insert Record. ‘Submit values from:’ ‘staff’ will be the default because staff is the name of the form. Choose the connDirectory connection and the staff table. Leave the rest as is.

Next to ‘After inserting go to:’, browse to the index file (or type ../index.php). You’ll want to go to that page after inserting a record so you can check to see if it has been inserted. Click OK. (The reason that you don’t need to assign values to the fields is because the text fields were named exactly what the column names are when the form was created.)

6. Now you need to add dynamic values to the building menu. Click on the menu button, then click on the Dynamic button in the Property Inspector.

7. Make sure the values in the Dynamic List/Menu dialog box match the image on the right and click OK. 8. You also need to add a dynamic value to the Building ID field so it will bind to the database. Click in the ID field. In the Property Inspector, click the Dynamic Data (lightning bolt) button next to the ‘Init value’ field. Click buildingID in the rsBuildings recordset. Click OK. 9. Save and test the page in a browser, then navigate through the staff data list to see if your entry made it to the list!

If you can add records – you need to be able to update them! An update page is very much like an insert-record page; the difference is that the form is already filled out with information from a particular record. First, you must tell the update page which record you want to update, so you’ll add a link to the details page that you’ve already created that points to the update record page.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 16

Dreamweaver CS3 Updating Database Records 1. Open detail.php. This page lists the details of a staff member when a record on the index page was clicked.

2. Click below ‘{rsDetails.buildingName}’ on a new line. Type Edit this information and highlight it. Click on the browse folder icon in the Property Inspector.

3. Navigate to the admin folder and select the file named edit.php - but don’t click Choose yet. Click the Parameters button, click the Name column and then type staffID. Press tab to move to the Values column and press the dynamic button. In the rsDetails recordset, choose staffID and click OK. Click OK to close the Parameters dialog, and now click Choose to add the link.

4. Save the page.

5. Now you can build the edit.php page. Open it from the Files panel (it’s in the admin folder).

6. Add a recordset in the simple mode. Name it rsStaff and choose your database connection. Choose the staff table.

7. Leave the All button selected, then add a filter: ‘staffID’. URL Parameter will automatically be selected as will its value of ‘staffID’. This filter instructs the recordset to retrieve only the record whose staffID matches the number passed in the URL parameter of the link. staff No sort is needed.

8. You can test the filter by clicking the test button and entering a random staffID number for a parameter. Click OK. 9. Create another Recordset for the buildings table; name it rsBuildings, select all columns, and sort by buildingID, Ascending just as you did for the Insert record page.

10. Place your cursor on the page below the Edit Records title. Go to Insert > Data Objects > Update Record > Record Update Form Wizard. It works in a similar way to the Insert Form Wizard.

11. Use these settings: Connection: connDirectory, Table: staff, select record: rsStaff, unique key: staffID, After updating go to: ../detail.php.

12. Delete staffID. Edit the rest of the labels in the form fields just as you did in the form wizard. All will be displayed as text except for the building names - they will be in a menu, as before.

13. For the building menu, display as Menu and click Menu Properties. This time you’ll get the information for the menu dynamically instead of building it yourself. Populate the menu from the database using the ‘rsBuildings’ recordset. Get the labels and values from ‘buildingName’.

Click OK. 14. Click OK again to close the Update form.

15. The wizard creates the table for you on your page. If you seem to have a huge space above the table, it’s okay. It will appear on the page properly.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 17

Dreamweaver CS3 16. Save the page, open the index.php page in a browser, select a staff member to see the detail page, and test your link to update the record.

Creating a Delete Record Page Obviously, you now need a way to delete a record if a staff member no longer works in the district. Begin by adding another link to the detail.php page.

1. Open the detail.php page if it’s not already open. Type Delete this record below the Edit link. Highlight it.

2. Click on the browse folder icon in the Property Inspector.

3. Navigate to the admin folder and select the file named delete.php - but don’t click Choose yet. Click the Parameters button, click the Name column and then type staffID. Press tab to move to the Values column and press the dynamic button. In the rsDetails recordset, choose staffID and click OK. Click OK to close the Parameters dialog, and now click Choose to add the link.

4. Save the page.

5. Instead of going through all the steps of making a recordset, you can just copy one from the edit.php page. Open that page, and open the bindings panel. Right-click (control-click) on the rsStaff recordset icon, and choose Copy from the pop-up menu.

6. Open delete.php from the admin folder, and the bindings panel. Control-click in an empty area and choose Paste. (If ‘paste’ is grayed out, click on the + button twice. You should be able to paste the recordset now.)

7. Click the triangle to expand the recordset, then insert the first_name, space, and last_name next to Record to Delete.

8. Click after {rsStaff.last_name} and press return to go to a new line and go to Insert > Form > Form (or use the insert menu, form tab).

9. A red dotted-lined box appears. This is the form container. Set the form’s Action property - a URL to the page that collects the form information. Click the action box in the Property Inspector, and type delete.php. Now, when this form is submitted, it will be sent to itself so it can process the delete record script which sends the viewer back to the index page when the record is deleted.

10. Choose Insert > Form > button. If the Input Tag Accessibility Attributes dialog box open, cancel it. In the Property Inspector, change the value to delete.

11. The Delete button will remove the record from the database, but you need to identify the staff member as well. A hidden form field will work here. Click to the right of the Delete button, and choose Insert > Form > Hidden Field.

This field needs to supply the staff ID. 12. With the hidden field icon selected, change its name in the Property Inspector from hiddenField to staffID. Click the dynamic data icon next to the Value box and select staffID from the rsStaff recordset. Click OK to close the Dynamic Data dialog box.

staff

13. In the Server Behaviors panel, click the + sign and select Delete Record. The Delete Record dialog box opens.

14. From the first menu, select Primary key value (probably the default). Choose connDirectory for the connection, and staff for the table. DW will select staffID for you because that’s the primary key for that table, but you need to indicate that the value is coming from a form, not a URL. I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 18

Dreamweaver CS3 Change the Primary key value to Form Variable. The staffID value is correct. Browse to the ../index.php file as the page to go to after deleting the record. Click OK. 15. Save and close the page.

16. Test by opening the index page in a browser.

In the real world, you won’t want just anybody to be able to insert, update and delete records. Next, you’ll set up a log-in system.

Password Protecting Web Pages User Authentication server behaviors can password-protect any page on your site. You can limit areas of your site to registered users only, allow staff members to access and update their contact information, create maintenance pages accessible to only administrators and even personalize pages with customized messages (“Welcome back, Sherry!”) To password protect pages, you’ll need several elements: • a database table containing user login information • a registration form for adding new users • a login form • pages to be password protected 1. Open login.php from the admin folder.

2. Click in the line below the title and add a form. Name it login in the Property Inspector.

3. Insert a text field. Make the ID user and the Label Email address:. Wrap with the label tag before the form item.

4. Click on ‘Email address:’ then click <label> in the Tag Selector, and choose paragraph in the Format pull-down menu in the Property Inspector to wrap <p> tags around the form element. Click after the email field and press return to go to the next line.

5. Add another text field, Id: password, Label: Password. Wrap with the label tag before the form item again.

6. Click in the password field and click the Password radio button in the Property Inspector. This doesn’t encrypt a password, but it appears as dots as the user types to protect from prying eyes.

7. Click after the password field and press return for a new line.

8. Insert a form button. You can cancel the Form Accessibility box. Click on the button and change the value of the button in the Property Inspector to Login.

9. Open the Server Behaviors window. Click the + button and choose User Authentication > Log In User. The first three items will be filled in for you: the name of the form, the user name field and the password field. If you had more than one form on the page you would have to tell DW which form and fields to use.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 19

Dreamweaver CS3 10. Select connDirectory from the ‘Validate using connection:’ pop-up menu. From the table menu, select users. In the Username column, choose email and in the password column choose password. Here you’re choosing the columns from the database where those bits of data are stored.

11. Browse to admin.php if the login is successful, and to denied.php if it is not. Both of these files are in the admin folder.

12. Finally, Restrict access based on Username, password and access level. Get the level from the access column.

13. Click OK. Save and try to login with anything you think of. The “Denied” page should appear (unless you’re a really good guesser!)

14. Login again, this time using website@school.com and admin as the email address and password. You’ve made it to the Add Record page where you can add, update and delete!

You’ve created the login page, but your pages are not protected yet. You’ll do that next.

Password-protecting the admin pages 1. The password-protection features offered by Dreamweaver CS3 require you to add a server behavior to each page that needs to be protected.

2. Open the admin.php page in the admin folder.

3. In the Server Behaviors panel, click + and choose User Authentication > Restrict Access to Page (you can also choose the User Authentication icon in the Insert bar > Data).

4. In the dialog box, click the radio button for Username, password, and access level. You want to designate the admin level for any changes to the database.

5. Click the define button next to the Select level(s) box, and type admin.

6. Add “If access is denied, go to:” denied.php. Click OK. 7. Back on the admin page, highlight the Log Out link in the lefthand sidebar.

8. In the Server Behavior panel, click the + button, and select User Authentication > Log Out User to add a script to the link.

9. The default of Log out when Link clicked “Log Out” is what you want to happen.

10. Browse to the main page of your site: index.php.

11. Click OK. Save and test the result of your work. You should be taken to the admin page IF you haven’t closed your browser between the last section of this tutorial and this one. If you see the denied page, log in again using website@school.com and admin. This will get you to the admin page, where you can test the Log Out link.

12. After you log out, test the admin.php page again. You should be immediately taken to the denied page - the admin page is successfully protected!

Of course - the most vulnerable pages for actually manipulating the data on the database also need to be locked down.

Follow the steps above to add protection to edit.php, add.php, and delete.php. Obviously, the denied.php and login.php pages shouldn’t be protected.

Your admin pages are protected, but everyone can still see the links that you added to the detail page for editing and deleting staff member data. It would be best to hide these links from anyone who isn’t logged in. It wouldn’t be terrible if someone clicked the links - they would be taken to the access denied page - but you wouldn’t want that to happen.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 20

Dreamweaver CS3 Displaying a portion of a page to logged-in users 1. Open the detail.php page. You’ll be doing a little programming in code view...

2. Click inside the text “Edit this information”; click <p> in the Tag selector at the bottom of the window.

3. Go to code view.

4. You’ll see something like the above code. Click before the highlighted <p> tag.

5. Type <?php if (isset($_SESSION[‘MM_UserGroup’]) && $_SESSION[‘MM_UserGroup’]==’admin’) { ?> and press return. The opening <?php tells the application server that some PHP code is coming. The web server sees that it’s not HTML code and that it needs to be processed by the application server. The “if” part of the code indicates a “conditional statement” In this case, it means that “if this user is logged in with an access level of ‘admin’, then the links will appear on the page”. To determine if the visitor is logged in with the admin access level, the code looks at a session variable called MM_UserGroup. This group was created when you (and Dreamweaver) created the log on behavior. This variable follows the user as he travels around the site. The code you just added checks to see if the session variable exists for this user (that’s the ‘isset’ part of the statement).

6. Click to the right of the closing </p> tag (just after “Delete this record”), press return and type <?php } ?> . This code concludes the php conditional statement. In other words, all of the HTML between the php tags will appear only if the user is logged into the site. You can find the code in a text file on the CD if necessary.

7. The code should now look something like this: 8. There’s one more thing to do on this page. Scroll to the very top of the page. Type <?php session_start(); ?> and press return to move the require database connection statement to the next line.

9. The first two lines of your page will now look like this: <?php session_start(); ?> <?php require_once('Connections/connDirectory.php'); ?>

10. The code you just added makes PHP turn on its session handling powers. Now this page will see any session variables set for the current visitor - whether the visitor is logged in and has admin privileges.

11. Save the page and test it in a browser. Because you logged out earlier, the links should be invisible to you.

12. Open the login.php page and open it in a browser. Log in as website@school.com and admin.

13. Now test the directorydetail page again. The links are back!

You could use this trick to add Log Out links to every page, but they would only be visible to admin users.

Whew! As you can see, Adobe has built powerful tools into Dreamweaver CS3. Hopefully this class gave you a good foundation with which to start creating dynamic, database driven websites!

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 21

Dreamweaver CS3 Resources Developing a Web application with Dreamweaver Setting up a PHP Environment with Dreamweaver phpMyAdmin Website MAMP and Wordpress PHP - a Beginner Tutorial What is phpMyAdmin?

Installing Apache PHP and MySQL in Windows Setting up PHP and MySQL on a Mac Building Better Forms with Dreamweaver CS3

Step-by-step Guides to Using Databases How Stuff Works - Relational Databases phpMyAdmin Flash Tutorials Using phpMyAdmin Learning SQL Using phpMyAdmin Captchas A CAPTCHA is a program that can generate and grade tests that humans can pass but current computer programs cannot. It’s a good idea to add a captcha to form pages to prevent spamming your database or email. Humans can read distorted text as the one shown below, but current computer programs can’t:

The term CAPTCHA (for Completely Automated Turing Test To Tell Computers and Humans Apart) was coined in 2000

by Luis von Ahn, Manuel Blum, Nicholas Hopper and John Langford of Carnegie Mellon University. At the time, they developed the first CAPTCHA to be used by Yahoo.

You can get a captcha for your site here: http://recaptcha.net/captcha.html

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 22

Dreamweaver CS3 More about managing the MySQL database with phpMyAdmin...

Dropping databases: From the main page, click on the Databases link. On the Databases page, click in the box next to the database you want to delete and click the little icon with the red X on it. You’ll get a dire warning that you’re destroying a complete database.

Click yes. You can also create new databases on this page. You can also remove a database by choosing it in the sidebar, and then clicking Drop in the menu bar.

Dropping a field in a table: Click on the red X in the Action area in a database field to delete it. If it’s designated a primary key, you cannot delete it without removing the primary key first. If you want to delete several fields, click on the checkboxes, click on the red X. You can delete a table from a database by clicking on the X in Action, or clicking in the checkbox and choosing Drop from the pull-down With selected menu.

To add a record to a database: Open a table in a database. Click Insert in the menu. Fill in the fields of information. Use Tab key to move to the next field.

If you have a primary ID key set to auto_increment, you do not need to enter an ID number in the field - mySQL will automatically generate the next ID number in sequence.

Editing a record Open a table, and click on the Browse button in the menu. Click on the pencil icon in the Action area of the table for a single record. You can check several records and click the pencil icon after ‘With selected’.

To sort data: Simply click on the name of a column in Browse mode and MySQL will sort the data. When you click away from the table, however, phpMyAdmin will automatically sort the database by Primary key again. You’ll have to resort again to view or print.

Printing data: Open a table and click on the Browse menu. You’ll see a box above the data table entitled Query results operations. You can Print, Print view with full text or Export.

As you browse and mange the database and look at different views, notice the yellow box at the top of many of the pages that appear. phpMyAdmin is sending SQL querys to the database so stuff happens. phpMyAdmin shows you the query in that yellow box. You can learn and begin to understand SQL syntax, and using DW will be that much easier!

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 23

Dreamweaver CS3 Exporting a Database: Backing up your data You should regularly back up your data. I’m sure that there are systems in place to back up the Web server that your database resides on, however, backing up the data within phpMyAdmin as a sql statement makes it easy to restore the data to MySQŁ 1. Open phpMyAdmin.

2. In the left sidebar, choose the database you wish to backup.

3. Click Export in the top menu.

4. In the Structure area, check ‘Add DropTable/DropView’. This will add a drop table statement so that if there is an existing table with the same name, it will drop it, then replace it with a new table by that name with the correct data.

You don’t want duplicate data.

5. In the Data section, check ‘Complete Insert’s. This has something do to about columns, but it works!

6. Check the Save as file box. Then click Go. Choose where you’d like to download the file.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 24

Dreamweaver CS3 Excel spreadsheet to MySQL database Before importing any data you need to set up your Excel spreadsheet(s) appropriately. Each Excel spreadsheet should correspond to a table in your database (or vice versa). In your Excel spreadsheet(s), make sure you create the same number of columns as exist for the corresponding database tables and make sure the columns are in the same order as shown by the phpMyAdmin interface. Open csv file in TextWrangler or TextEdit. Remove the first line of headings. If you are planning to include an auto-increment field for a primary key in your MySQL database, you must have a column with consecutive numbers in the csv file. To add that without having to type every id number in Excel, control-click on the first column and choose insert. Put the number 1 in cell A1. In cell A2, enter =A1+1 and press return. Click and drag from cell A2 to the bottom of your data cells. Go to Edit > Fill > Down. All the numbers will be filled in consecutively. When you are ready to upload your data into the database, export / save your spreadsheet using the Comma Separated Values (CSV) format. This will enable you to import the data into the MySQL database.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 25

Dreamweaver CS3 After logging into phpMyAdmin, you should see a pull down box on the left of the page under “Database”. Click on it and select the appropriate database. If you have no databases in the list you can create one in phpMyAdmin. After selecting a database, you will see any tables that have been created for that database in the left menu as well as in the main window.

To create a table in the database: If you already have data in an Excel spreadsheet and are creating a table to import it to, you must create the table fields exactly as they are in the spreadsheet and make sure the columns are in the same order. Each field must have a type and length/value filled in.

To import data into a database table, click on that table’s name in the phpMyAdmin interface. You then need to click on “Import” at the top of the main window. This provides the interface through which you can upload data to the table you have selected. Click on “Browse...” to locate the .csv file you wish to upload. Then select “CSV” as the Format of imported file (it should be automatically selected). Under CSV options, “Fields terminated by” should be changed to a comma.

Leave other settings alone. “Column names” can be left blank (assuming your Excel spreadsheet contained the same columns in the same order as the corresponding database table). Click on “Go” and the file will be imported. If you see that the Import failed, you can delete all the records in the table and start afresh by clicking on “Empty” at the top of the main window. You can then proceed to upload an amended .csv file again.

I L - T C E : I n t e r m e d i a t e D r e a m w e a v e r

February 27, 2008 26