close ad
 
Important WebAssist Announcement
open ad
View Menu

Web development tutorial

Manage relational tables

Tutorial created by Ray Borduin, WebAssist

Categories: Data Bridge

rating
6 ratings | Sign in to rate this tutorial | View comments

Managing relational tables has always been a struggle for both fledgling young web developers and grizzled old salts alike. Luckily for everyone who needs to link database tables, Data Bridge simplified the process, making it easy for just about everyone.

In this tutorial we're going to teach you what you need to do, and why, when it comes to using Data Bridge to manage your relational database tables.

We're going very easy on this one, and we're creating a basic music album site. You might want to rush right in with your own database and site, but we suggest following the tutorial to ensure you have the foundations needed.

next sectionarrow down
arrow downWhat you need to start

  1. A dynamic site set up within Dreamweaver
  2. A blank database
  3. Dreamweaver CS3 or later
  4. Data Bridge installed and activated in Dreamweaver
  5. The Blue Sky Music database files, which can be downloaded here blue_sky_music.zip

next sectionarrow down
arrow downConfigure the Database

The first step in this long journey is creating a database with some basic data. This will give us a central point to build our application around.

We're using the classic WebAssist staple; the Blue Sky Music database.

  1. Download the Blue Sky Music zip file
  2. Extract the files to an easily accessable directory
  3. Open your MySQL database manager
  4. Open the blue_sky_music.sql file
  5. Copy the contents of the file
  6. In your blank database, run the Blue Sky Music SQL query
  7. Create a Connection in your Dreamweaver site to the newly populated database



Alternatively, you can use the Import feature if your SQL manager supports it.

Now that we have this database set up, we can start making the files needed to work within our database.

next sectionarrow down
arrow downCreate Insert/ Update Pages

To use the data we just added to our database, and to manage these relational tables, we're going to need Insert and Update pages. Just so we cover everything, we'll create the Results and Detail pages, too.

  1. Click WebAssist > Database > DataAssist Pages
  2. Use New From Blank as the Application Type. We're going pretty custom on this.
    You CAN select a design, but for the sake of this tutorial, we're going to stick with Basic
  3. Make sure the connection to the blue_sky_music database is selected
  4. Select the items table
  5. Make sure ItemID is the Key Column
  6. Click the Manage Pages tab
  7. Click the Add Pages ( + ) button
  8. Select Insert
  9. Click OK
  10. On the Insert Page dialog, click the Add Columns ( + ) button
  11. Select the ItemName, ItemShortDesc, ItemLongDesc, ItemPrice, and ItemSKU columns
    These columns are selected just so we have a little something to work with

  12. Click OK
  13. Click the Add Pages ( + ) button
  14. Select Update
  15. Click OK
  16. We're basically making the exact same page as Insert, only for Update
  17. On the Update Page dialog, click the Add Columns ( + ) button
  18. Select the ItemName, ItemShortDesc, ItemLongDesc, ItemPrice, and ItemSKU columns
    Just like before, these columns are selected just so we have a little something to work with

  19. Click OK
  20. Click the Add Pages ( + ) button
  21. Select Results
    This page is so you can easily view newly added and previously existing entries in your database

  22. Click OK
  23. On the Results Page dialog, click the Add Columns ( + ) button
  24. Select the ItemName, ItemShortDesc, ItemLongDesc, ItemPrice, and ItemSKU columns
  25. Click OK
  26. Click the Add Pages ( + ) button
  27. Select Details
    This page is so you can view individual entries

  28. On the Details Page dialog, click the Add Columns ( + ) button
  29. Select the ItemName, ItemShortDesc, ItemLongDesc, ItemPrice, and ItemSKU columns
  30. Click OK
  31. Click Finish
  32. Click OK without saving the preset
  33. Save everything

next sectionarrow down
arrow downAdd genres to the Insert Page

Now that your pages are made, we're going to go ahead and update your Insert page to include music genre information. This will be the two sides of the linked databases: genres and items. Each item will be able to have multiple genres attached to it, and each genre will be connected to all of its related items.

Step 1: Create an area to hold the genre list

  1. On the Insert page, switch to Split view
  2. Select the last text box on the insert form in the design side of the view
  3. On the code side of the view, find the div with the class "lineGroup" that's containing the text box
    Each DataBridge Application type can create the divs used in the page code a little diffferently. If you've used another Application type, the class name in the above div might be slightly different..

  4. Create a new div after the above mentioned lineGroup div. You can enter it manually, or paste in the following code:
    This is going to create a new container for the checkbox area. The lineGroup class contains the data for how the div is being displayed
    <div class="lineGroup"> 
    </div>

  5. Add the following code right after the closing quotation mark of the class in the div we just created:
    This will create a CSS style that gives the div a width of 600 on the page and hides all overflow
    style="width:600px;overflow:hidden;"

  6. Now we're going to set a little label for the div we just created so people know it's a list of genres. Enter the following code at the end of your opening div tag:
    Genres:
    <br />

  7. Now we need to create a span to hold the checkbox we're going to use for each genre. Enter the following code right after the <br> tag you just entered above:
    This span will house the checkbox that will be used for each genre, as well as the label for each genre so we can repeat the span as many times as needed to display every available genre. This saves us having to manually enter each one.
    <span>
    </span>

  8. Last but not least, add the following style to the opening span tag:
    This style will make each checkbox/ genre label combo in its own little container that is 200 pixels wide, and aligns them to the left. This will show the genre list in 3 columns, with as many rows as needed.
    style="width:200px;float:left;"

  9. Save your page
    At this point your entire div should look like this:
    <div class="lineGroup" style="width:600px;overflow:hidden;"> 
         Genres:
         <br /> 
         <span style="width:200px;float:left;">
         </span>
    </div>

Step 2: Add a checkbox to the genre list area

  1. Click your cursor in the span tags we created above
  2. Click Insert > Form > Checkbox from the menu bar
  3. Enter genres as the ID
  4. Set the Style to No label tag

  5. Click OK
  6. Click on your newly added checkbox
  7. In the Properties panel, enter 1 as the Checked Value for the checkbox
  8. Save your page

Step 3: Create a Recordset for genres

  1. In the menu bar, click Window > Bindings
  2. In the Bindings window, click the Add Binding ( + ) button
  3. Select Recordset (Query)
  4. Make sure the Recordset dialog is set to Advanced mode by clicking the Advanced... button
  5. Enter rsGeneres as the name of the Recordset
  6. Make sure the connection to the blue_sky_music database is selected
    This will populate the Database items box with everything in your database
  7. Expand the Tables list
  8. Select genres from the expanded tables
  9. Click the SELECT button
    This will add the following SQL query to the SQL box, which pulls all available genres from the genre list in the database:
    SELECT *
    FROM genres

  10. Click the OK button to add the Recordset to your page
  11. Save your page

Step 4: Make the checkbox dynamic

  1. In Code view, find the genre checkbox that we created earlier
  2. Select the 1 that we set as the value earlier
  3. Enter the following code in the place of the current 1 value of the checkbox so the relational tables know the selected GenreID
    This line of code will give the checkbox a value based on the ID for each genre stored in the database, for as many genre IDs exist. These IDs are being pulled from the database with the help of our handy little rsGenres recordset.
    <?php echo $row_rsGenres['GenreID']; ?>

  4. After the checkbox field, enter the following code to show the appropriate genre's name next to the checkbox
    This pulls the genre name associated with the above GenreID that's stored in the database. This name is being pulled from the database with our rsGenres recordset
    <?php echo $row_rsGenres['GenreName']; ?>

  5. At this point your entire div should look like this:
    <div class="lineGroup" style="width:600px;overflow:hidden;" > 
         Genres:
         <br /> 
         <span style="width:200px;float:left;">
              <input name="genre" type="checkbox" id="genre" value="<?php echo $row_rsGenres['GenreID']; ?> " /> 
              <?php echo $row_rsGenres['GenreName']; ?> 
         </span> 
    </div>

  6. Save your page

Step 5: Repeat the checkbox for each genre

  1. In code view, select the span that goes around your checkbox
  2. In the Server Behaviors panel, click the Add Server Behavior ( + ) button
  3. Select WebAssist > DataAssist > Repeat Selection
  4. Select rsGenres for the Recordset field
  5. Select All records for the Repetitions field

  6. Click OK
    This applies the Repeat Selection Server Behavior to the checkbox, and will show as many checkbox/ genre labels as there are in the database
  7. Save your page

Step 6: Apply 'Insert Multiple Records' to the genre checkboxes.

  1. From the Behaviors window, select WebAssist > DataAssist > Insert > Multiple Record Inserts.
  2. For the 'Repeated form:' selection, make sure 'Repeat Selection (rsGenres, All records)' is chosen.
  3. Use the 'blue_sky' connection and the 'itemgenres' table.
  4. Chose your results page for the 'Go to' option.
  5. Click on the 'Bindings' tab.
  6. Select the column 'ItemGenreItemID', click the lightning bolt, and choose the Session variable 'WADA_Insert_items'. The data type is Numeric.
  7. Select the column 'ItemGenreGenreID, click the lightning bolt, and chose the form item 'genres'. (Your form may be named something like 'Insert_Basic_Default'.) The data type is Numeric.
  8. Click Finish.
  9. Save your page.

next sectionarrow down
arrow downAnd now the Update page

It's the Update page's turn to get the genre list treatment. This process is exactly the same as on the Insert page.

Step 1: Create an area to hold the genre list

  1. On the Insert page, switch to Split view
  2. Select the last text box on the insert form in the design side of the view
  3. On the code side of the view, find the div with the class "lineGroup" that's containing the text box
    Each DataBridge Application type can create the divs used in the page code a little diffferently. If you've used another Application type, the class name in the above div might be slightly different..

  4. Create a new div after the above mentioned lineGroup div. You can enter it manually, or paste in the following code:
    This is going to create a new container for the checkbox area. The lineGroup class contains the data for how the div is being displayed
    <div class="lineGroup"> 
    </div>

  5. Add the following code right after the closing quotation mark of the class in the div we just created:
    This will create a CSS style that gives the div a width of 600 on the page and hides all overflow
    style="width:600px;overflow:hidden;"

  6. Now we're going to set a little label for the div we just created so people know it's a list of genres. Enter the following code at the end of your opening div tag:
    Genres:
    <br />

  7. Now we need to create a span to hold the checkbox we're going to use for each genre. Enter the following code right after the <br /> tag you just entered above:
    This span will house the checkbox that will be used for each genre, as well as the label for each genre so we can repeat the span as many times as needed to display every available genre. This saves us having to manually enter each one.
    <span>
    </span>

  8. Last but not least, add the following style to the opening span tag:
    This style will make each checkbox/ genre label combo in its own little container that is 200 pixels wide, and aligns them to the left. This will show the genre list in 3 columns, with as many rows as needed.
    style="width:200px;float:left;"

  9. Save your page
    At this point your entire div should look like this:
    <div class="lineGroup" style="width:600px;overflow:hidden;"> 
       Genres:
         <br /> 
         <span style="width:200px;float:left;">
         </span>
    </div>

Step 2: Add a checkbox to the genre list area

  1. Click your cursor in the span tags we created above
  2. Click Insert > Form > Checkbox from the menu bar
  3. Enter genres as the ID
  4. Set the Style to No label tag

  5. Click OK
  6. Click on your newly added checkbox
  7. In the Properties panel, enter 1 as the Checked Value for the checkbox
  8. Save your page

Step 3: Create a Recordset for genres

  1. In the menu bar, click Window > Bindings
  2. In the Bindings window, click the Add Binding ( + ) button
  3. Select Recordset (Query)
  4. Make sure the Recordset dialog is set to Advanced mode by clicking the Advanced... button
  5. Enter rsGenres as the name of the Recordset
  6. Make sure the correct connection is selected
    This will populate the Database items box with everything in your database
  7. Expand the Tables list
  8. Select genres from the expanded tables
  9. Click the SELECT button
    This will add the following SQL query to the SQL box, which pulls all available genres from the genre list in the database:
    SELECT *
    FROM genres

  10. Click the OK button to add the Recordset to your page
  11. Save your page

Step 4: Make the checkbox dynamic

  1. In Code view, find the genre checkbox that we created earlier
  2. Select the 1 that we set as the value earlier
  3. Enter the following code in the place of the current 1 value of the checkbox so the relational tables know the selected GenreID
    This line of code will give the checkbox a value based on the ID for each genre stored in the database, for as many genre IDs exist. These IDs are being pulled from the database with the help of our handy little rsGenres recordset.
    <?php echo $row_rsGenres['GenreID']; ?>

  4. After the checkbox field, enter the following code to show the appropriate genre's name next to the checkbox
    This pulls the genre name associated with the above GenreID that's stored in the database. This name is being pulled from the database with our rsGenres recordset
    <?php echo $row_rsGenres['GenreName']; ?>

  5. At this point your entire div should look like this:
    <div class="lineGroup" style="width:600px;overflow:hidden;"> 
        Genres:
         <br> 
         <span style="width:200px;float:left;">
             <input name="genre" id="genre" value="<?php echo $row_rsGenres['GenreID']; ?> " type="checkbox"> 
              <?php echo $row_rsGenres['GenreName']; ?> 
         </span> 
    </div>

  6. Save your page

Step 5: Repeat the checkbox for each genre

  1. In code view, select the span that goes around your checkbox
  2. In the Server Behaviors panel, click the Add Server Behavior ( + ) button
  3. Select WebAssist > DataAssist > Repeat Selection
  4. Select rsGenres for the Recordset field
  5. Select All records for the Repetitions field

  6. Click OK
    This applies the Repeat Selection Server Behavior to the checkbox, and will show as many checkbox/ genre labels as there are in the database
  7. Save your page

next sectionarrow down
arrow downRemove redirects

Our next step is simply to remove the redirect functionality that was created when the pages were created. The Manage Relational Table Server Behavior is going to handle this later on, and we don't need any conflicts.

Step 1: For the Insert page

  1. Open the Insert page
  2. Open the Server Behaviors panel
  3. Double click on the 'Insert Record (items)' Server Behavior to open it
  4. Clear the 'Go to' field in the 'After Insert' section
  5. Now write down the 'Store as' value under the 'Save Inserted ID in Session' section
    This value is the name of a Session variable that was created to store the ID of the record that was just inserted. Later, the Manage Relational Table will use this to know which record is being talked about in regards to the relationship between the item being inserted and the genres with which it's associated
  6. Click OK
  7. Save your page

Step 2: For the Update page

  1. Open the Update page
  2. Open the Server Behaviors panel
  3. Double click on the 'Update Record (items)' Server Behavior to open it
  4. Clear the 'Go to' field in the 'After Insert' section
  5. Click OK
  6. Save your page

next sectionarrow down
arrow downAdd variables

For this step, we need to add a variable to each page to allow the Manage Relational Table Server Behavior to know which itemis either being inserted, or updated. To do this, we need a session variable on the Insert page to hold the item being inserted, and a URL variable on the Update page to hold the ID of the item being updated.

Step 1: For the Insert page

  1. Open the Insert page
  2. On the Bindings panel, look for a Session variable named the same as the value we wrote down when looking at the insert server behavior
    In this case, the value should be 'WADA_Insert_Items', but this value will change in the future when you do your own project
  3. If it exists, it was created by the Insert server behavior, and you can skip the rest of this section, if not, click the Add Binding ( + ) button
  4. Select Session Variable
  5. On the Variable dialog that appears, enter the value we wrote down from the Insert server bahavior earlier in the Name field
    In this instance, the name will be 'WADA_Insert_Items', but this WILL change when you are doing your own project in the future
  6. Click OK
  7. Save your page

Step 2: For the Update page

The reason we are adding this update variable to the page is to prepopulate the checkboxes we added earlier with the correct genres that are available to us.

  1. Open the Update page
  2. In the Server Behaviors list, double click the 'Recordset(WADAitems)' Server Behavior to open it
  3. Under the variables list, click on 'ParamItemID'
  4. Look at the values for 'ParamItemID' and find the Run-Time Value
  5. Look for the text within the single quotes of the $_GET text. An example is $_GET['thistexthere'].
    In this case the value will be ItemID. This value is the current ID of the record being updated. This will be used in the Manage Relational Table later to create the relationship between the item being updated, and its associated genres

  6. In the Bindings panel, look for a URL variable named the same as the value we wrote down when looking at the Recordset(WADAitems) server behavior
    In this case, the value should be ItemID, but this value will change in the future when you do your own project
  7. If it exists, it was created by the Update server behavior, and you can skip the rest of this section, if not, click the Add Binding ( + ) button
  8. Select URL Variable
  9. On the Variable dialog that appears, enter the value we wrote down from the Update server bahavior earlier in the Name field
    In this instance, the name will be ItemID, but this WILL change when you are doing your own project in the future
  10. Click OK
  11. Save your page

next sectionarrow down
arrow downUpdate page MRT

It's time to get to the heart of this tutorial, the Managing Relational Table server behavior. This section will walk you through the proper set-up and configuration of MRT.

Step 1: Insert the Manage Relational table Server Behavior

  1. Open the Update page
  2. On the Server Behavior panel, click the Add Server Behavior ( + ) button
  3. Select WebAssist > DataAssist > Manage Relational Table

Step 2: Database and Redirect

This will redirect back to the Details page after successful update and send the ItemID as the update URL variable data on redirect so you can see your newly updated item.

  1. On the Manage Relational Table dialog, set the Relational table field to 'itemgenres'
  2. Click the Browse folder button next to the 'Go to' field
  3. Select the Details page
  4. Click the 'Parameters...' button
  5. Under the Name column, enter the name of the URL variable that either we created, or the Update Server Behavior created
    This variable is used by the Details page to know what product to display. Remember, in this case it's 'ItemID', but that will change depending on your application
  6. Click in the Value column
  7. Click the Dynamic Data (lightning bolt) button
  8. Expand the URL field in the Field list
  9. Select the URL variable that either we created, or the Update Server Behavior created
    Remember, in this case it's 'ItemID', but that will change depending on your application
  10. Click OK
  11. Click OK again
  12. Click Choose
    This is how your wizard should appear on this step:

  13. Click Next

Step 3: Main Table Join

This is going to enter the updated item's ID into the relational table's 'ItemGenreItemID' field for each genre the item has.

  1. Set the Foreign key column value to 'ItemGenreItemID'
    In this case, 'ItemGenreItemID' is being used, but in future cases, you're going to select the Foreign key column value of the main table being used. The Main table will be the actual item that you are attaching properties to. The properties of the item themselves will be the Options table
  2. Set the Data type field to Numeric
  3. For the Key value field, click the Dynamic Data (lightning bolt) button
  4. Expand the URL field in the Field list
  5. Select the URL variable that either we created, or the Update Server Behavior created
    Remember, in this case it's 'ItemID', but that will change depending on your application
  6. Click OK
    This is how your wizard should appear on this step:

  7. Click Next

Step 4: Options Table Join

This will enter the genre ID for each genre the item is related to.

  1. Set the Foreign key column value to 'ItemGenreGenreID'
    In this case, 'ItemGenreGenreID' is being used, but in future cases, you're going to select the Foreign key column value of the options table that stores the possible properties of the Main table
  2. Set the Data type field to Numeric
  3. For the Options formfield field, Select 'genres' in Repeat Selection (rsGenres, All records)
    This is how your wizard should appear on this step:

  4. Click Next
    This is how your wizard should appear on this step:

  5. Click Finish
  6. Save your page


And... *dusts off hands* The Update page is finished.

arrow downWhat to do next...

Well done, my dear friend. You're my favorite web developer, you know that? *Shh* Don't tell anyone, though. It'll be our secret.

You now have the power inside of you to utilize the Manage Relational Table Server Behavior on your own site, so get cracking!

arrow downReviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

Sign in to add comments
rating
6 ratings | Sign in to rate this tutorial
rating

Peterson Design Studio - jefferis: 10 Years, 5 Months, 3 Weeks, 3 Days, 20 Hours, 14 Minutes ago

This is very technical, but a little short on the "why" of each step and what it is designed to accomplish.

: 10 Years, 5 Months, 1 Hour, 24 Minutes ago

I'm using Dreamweaver CC and "On Step 2: Add a checkbox to the genre list area" I do not get the dialog box asking for the checkbox ID, Label and style. On the properties panel at the botton of the window there is no ID field.

Thanks

Jason Byrnes: 10 Years, 4 Months, 4 Weeks, 1 Day, 23 Hours, 5 Minutes ago

Adobe have removed that Dialog box in DW CC. In the property Inspector, you should enter genres as the Name, it will use the name you enter for both the ID attribute and the Name attribute.

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Everything else!

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

go to page top