Getting started with MySQL and dynamic websites

Step 1: Start the site definition

In Dreamweaver go to Site -> New Site.

you can edit an existing site definition by going to Site -> Manage Sites. Select the site you wish to modify, then click the edit button.

Step 2: Enter the local Information

In the first section of the site definition, you will set the Local Information. This sets the Site Name, and local path for files in your site.

If you are using a local testing server, It is recommended to set the local path to be a folder inside the site root folder. For example: I have MAMP installed on my Macintosh computer. In MAMP, the site root folder is:
/Applications/MAMP/htdocs

when creating a new site in Dreamweaver, I like to create a subfolder of htdocs:
/Applications/MAMP/htdocs/dynamic_site

and use that as the local path

When using XAMPP in a windows system, it may be that the site root is mapped to:
C:\xampp\htdocs

so the new site folder would be created under that location:
C:\xampp\htdocs\dynamic_site

It's important to become familiar with the testing server package you have chosen and where the site root is.

In the case where you do not install a local testing server and you opt to use a remote hosted site for testing, the local path setting is not that important, just make sure the path is web safe (in other words, use only alpha numeric characters in any folder names).

In this screen shot, i am using DW CS5.5 on a Macintosh system with MAMP installed:

Step 3: Servers

The next setting is to connect to servers. In DW CS 4 and above, this done by selecting the servers section:

and clicking the plus icon to define a new server connection.

For the Name you can enter the same name as the Site definition, When using a local testing server, set Connect Using to "Local/Network"

Set the Server Folder to the same location as the local files set in the initial Site Settings.

The Web URL will be:
http://localhost/dynamic_site

Click the advanced button and set the Application model to PHP / MySQL

once the connection is defined, there are check boxes to set the connection as a testing or remote connection, check the testing option.

Step 4: add images folder

once the site has been defined, add the images folder in the linked attachment to the site root, these are image files for the items in the blue sky music catalog we will be using
images.zip

Step 1: Create a New PHP page

Go to File -> New. In the File Creation dialog, select Blank Page. From the page type section, select PHP and click the create.

Step 2: Save the page to your site

Go to File -> Save.

In the Save As option, enter "recordset.php"

In the Where section, select the folder for your site.

Step 3: Create the connection to the database

1) open the database panel by going to Window -> Database.

2) In the database panel, click the plus button and select MySQL Connection

3) enter host, username and password. When using a local testing server, the host name will be "localhost", the default user name will be "root". the password will depend on the package you use, by default with MAMP, the password is blank, but with XAMPP, the default password is "root"

4) click the select button next to database and select the blue_sky_musing database we created in the previous section.

5) Click the test button

6) Click save

Step 1: Open the Server Behaviors panel

Go to Window -> Server Behaviors to open the server behaviors panel

Step 2: Start the Recordset dialog

In the Server Behaviors panel, click the plus button and select Recordset

Step 3: Set the Recordset properties

In this step we will create a simple recordset, if the recordset dialog is in advanced mode, click the Simple button to switch it to simple mode.

For the Name, enter rsItems.

From the Connections list, select the connBlueSkyMusic connection.

In the Columns section, change the All radio option to Selected. then using the Command button (Control on Windows) Select the itemName, itemShortDesc, itemPrice, and itemThumb columns

In the Filtering section, select:
'itemPrice' '<'
'Entered value' '50'

For the sort, select:
'itemPrice' 'ascending'

then click on the test button:

Step 1: Create initial table

Add a table with 1 column ad 1 row.

go to Insert -> Table. in the table settings use 1 Column, 1 Row.

Step 2: Insert a table

Place your cursor in the one cell then Go to insert -> Table.

for the table settings, use 4 Rows, 2 Columns, set the width to 400 Pixles. And select a left header:

this will add a secondary table

Step 3: Populate the table

In the left side column, enter labels:
Item Name, Item Price, Item Description, Item Image

in the column on the right, place your cursor, then open the bindings panel and expand the rsItemsRecordset, select the corresponding column and click the Insert button.

Step 4: Add the item image

for the item image column, go to Insert -> Image. In the Image source Dialog Box, click the Data Sources button:

From the Dynamic data, selct the itemThumb column from the rsItems Recordset:

in the code section, add the images/thumbs/ path in front of the dynamic binding code:

Step 5: Add a repeat region around the intila table row

In Split View, highlight the Item name Label:

in the tag selector, select the main table <tr> tag:

then add the repeat region behavior: in the server behaviors panel, click the plus button then select Repeat Region.

From the recordset select list, select the rsItems recordset. and select to show all records:

Step 6: Save the page and preview it in the web browser

Go to File -> Save to save the page, then File -> Preview in browser to see the page live:

Step 1: edit the recordset

open the server behaviors panel (Window -> Server Behaviors) and double click the rsItems recordset in the list.

Step 2: change from simple to advanced

in the recordset dialog, click the advanced button:

the advanced recordset looks like this:

Step 3: add an additional condition

initially, the where clause of the recordset is:

WHERE ItemPrice < 50

to add an aditional filter, we need to add to the where clause.

In the Database Items section, Expand tables

Expand the Items Table. Select the itemCatID column and click on the WHERE button:

the where cluase now is:

WHERE ItemPrice < 50 AND items.ItemCatID

we need to add the condition by changing it to:

WHERE ItemPrice < 50 AND items.ItemCatID = 1

On testing the recordset, it returns the following results:

Unlike the simple recordset, this now only returns 6 results since it is only returning records for category 1.

Preview the recordset page in the browser again, and it will show the category1 items only:

Step 1: Create a new page to show the details on

This is similar to creating the first recordset.php file in our site.

Go to File -> New to open the new file wizard. from the file types list select PHP and click the create button.

Go to File -> Save as to save the file to the site and name it details.php

Step 2: Edit the recordset

On the recordset.php page, we need to edit the recordset to return the ItemID column.

in the server behaviors list, double click the recordset to edit it. It should still be in advanced view.

In the database items section, expandd table, expand the Items table and select the ItemID column and click the select button, this will add the ItemID column to the select statement:

Step 3: Create the link to the details page

On the recordset.php page, select the image place holder in design view. In the property inspector, Click the folder Icon next to link

in the file browser, selct the details page, then click the parameters button

Set the Name to "ItemID" and click the lightning bolt next to value, from the dynamic bindings window, select the ID column from the rsItems recordset.

click ok in the binding window, ok again in the parametes window, then chose in the file browser window to get back to the page and have the link applied to the image.

Step 4: create the lookup recordset on the Details page

open the detail.php page and go tothe server behaviors panel, click the plus button and Recordset.

We can use Simple view for this recordset

Name the recordset rsItemDetail

From the tables list, select the Items Table.

In the Filter section, set the filtering to use the ItemID column, A URL variable and name the URL variable ItemID to match the name used in the link

Step 5: Add a Table to display the item details

Similar to recordset page, we will add a table to show the details
Go to Insert - > Table, set the Columns to 2, and Rows to 4. Set the width to 400, and use the left column as the header

Step 6: Populate the table

In the left side, add the labels for the columns you will use, on the right side, use the bindings panel, ssplace your cursor in a cell, select the column to display and use the Insert button to add the binding to the page

Step 7: Add the item Image

Place your Cursor In the item Image cell, Go to Insert -> Image. In the oimage source window, click the data sources button:

From the bindings, expand the rsItemDetail recordset and select the ItemImage column

Click OK to apply the image.

In the Property inspector, add the image path in front of the dynamic binding code in the image source

Step 8: Save and preview

save both pages, and preview the recordset page in the browser. the images should be a click-able link that brings you to the detail page