By: Richard Tisdale

March 7th, 2003

Reader Rating: 9

Website designers and managers are finding they no longer need to be expert programmers to reap the benefits of a behind-the-scenes database. In Adobe GoLive 6.0, it's a simple matter:

• run a dynamic data wizard,
• use a few drag-and-drop commands, and
• understand some basics

...and you're off and running with dynamic data.

This tutorial shows you how to do just that, using Adobe GoLive 6.0 in tandem with a simple Microsoft Access database.

Once you understand this process, you'll find useful and interesting ways to modify it. We've used a similar database to drive an alumni directory for a university. Individuals can log in and dynamically update the information the school has on file for them. Similarly, any business with a set of known clients can let individuals view certain data and keep it current. The time it takes to master this technique is negligible when you consider that the university's administrators, for example, no longer have to manually enter address or phone number changes for alumni.

Step 1. Covering the Basics

First things first: I'll assume you have a hosting account with an ISP (Internet Services Provider) or otherwise have access to server space in which to publish a site or sites. I'll also assume you have basic familiarity with GoLive software, and plan to use GoLive 6.0. Third, an entry-level understanding of how a database works is helpful. Fourth, go ahead and update to GoLive 6.0.1 at: http://www.adobe.com/products/golive/main.html [1]. It's well worth it!

The first question to answer is: does your Web host allow the use of ASP, PHP, and/or MySQL data on their servers? Major ISPs often do. Check it out with them, because if they do, GoLive will automatically confirm that everything you need is in place. This means you can skip the entire procedure in the GoLive 6.0 manual "Preparing a site on a Web server." If your ISP or server isn't ready, you will need to set up your own server per the manual, or find a server that can accommodate your database. Also, GoLive includes pre-configured server environments that you can run on the same system as the GoLive client. See your Adobe Web Workgroup Server CD for more information on installing the preconfigured servers.

To get started, create a blank GoLive site. Don't worry about how it will look. At this point, I map out the flow of my site's pages using GoLive Design Diagrams, but all you need to do is create a page or two to get the database running, as we'll discuss in Step 2.

If you prefer, you can download a ZIP file containing our example sites using both ASP and PHP. Download the sample sites here [2].

Step 2. Getting Ready for Takeoff

The Database

Now you need a database. It will act as your "data source" in GoLive, and we'll explain what that is in Step 4. We've created a simple Microsoft Access database and included it with our ASP sample site, but of course, you can create one yourself. Whatever database tool you choose (Access is just one example), you'll need to know what data this file will hold based on the information you want to store and/or present to your viewers.

Our sample database has one table that contains five fields: an AutoID field that gives each record a unique identifier, plus four user-defined fields for:

• First Name
• Last Name
• Phone
• Password

The database should also be password-protected (we didn't password-protect our sample, though, so you can use it as a starter), which you can do in Access by choosing Tools > Security > Set Database Password.

When your database has these or similar elements, set it aside.

Placeholder Pages in GoLive

Now, back to your GoLive site. You'll need to mock up two main pages with placeholder content. At this point don't worry about Dynamic Content, just focus on visual layout. The firspage is a Search page where a person can enter keywords and search your directory. Name this page index.html or whatever suits you. The page should contain the following minimum elements:

• Search Form with text field and submit button
• Table to display the search results in a row by row format
• Form for login using last name and password in order to edit a record

The second page is an Edit page, where an individual who logs in can update their record. Call this page edit.html or any other appropriate name. You can work with our sample pages if you like, or at least view them to see the page elements you'll need.

Step 3. Going Dynamic

You'll now use the Dynamic Site Wizard in GoLive to make your site dynamic.

From the Site menu, choose Settings > Dynamic Content > Dynamic Site Wizard. Set the server language (ASP in this example, but you may have chosen PHP or JSP). Your server connection is most likely FTP (file transfer protocol), but it could also be WebDav or Site Is Served Locally if you run a Web server, including those shipping with GoLive 6.

If you'll use FTP and your settings aren't already established, create a connection under the FTP pop up menu, Edit Server, using the information provided by your ISP (ask them for it if you don't have it). Next, the Wizard will prompt you for the URL root of your site. This is simply the address of the home page for this test site; e.g. http://www.mysite.com/test.

Now the Wizard will ask what Security Options you want. GoLive automatically identifies your current IP address and will allow that address unrestricted access to the site files. If you're on a dial-up modem, however, your IP address is different each time you dial in. In this case, select "Allow Anyone," which ensures you can access and edit the site for now. You can adjust the security options later when you're ready to launch.

GoLive makes use of IP authorization to prevent unauthorized individuals from tampering with your files. You'll find a file in your config folder in which you can "include friends" by listing trusted IP addresses. You can open this file (e.g. config > include > friends.asp or similar) in a text editor at any time after the site has been made dynamic to see the instructions on adding other friendly IP addresses, and disabling IP authorization altogether.

Skip the Wizard's final question on Data Sources for now -- we'll get to that momentarily. Enter Finish on the last screen of the Wizard, and guess what... your site is now dynamic!

At this point, GoLive has automatically uploaded test files and performed a server check. If GoLive detects any problems, it will notify you, and you can back up to the point in the process where the correction needs to take place.

Step 4. What is a Data Source?

If you're working with dynamic data for the first time, you may be wondering what the difference is between a "database," a "data source," and a "content source." They're not necessarily interchangeable, so a brief explanation will help.

Database: this file contains your data organized into one or more tables. Our sample is a Microsoft Access file (.mdb), but you may have chosen to work with Microsoft SQL Server (ASP), Oracle 8 (ASP), MySQL 3.23 (PHP and JSP), or Oracle 9 (JSP).

Data Source: this file points to a database and tells your site how to access it. You create a Data Source in the Dynamic Content area of the Site Settings. GoLive then places the Data Source file in the config > datasources folder of your Dynamic Site.

If your Microsoft Access database is not password-protected, as in this example, the database itself acts as the Data Source and you don't have to create a separate one. You're more likely to want to password-protect your database in a real life situation, so we'll make a separate Data Source for it below.

Content Source: this is a query from your dynamic page to a database in order to provide content for that page. Pages can have multiple Content Sources that provide different content for that page. For instance, you'll be creating Content Sources (queries) that return all records, or return only records with certain criteria, like only those records with a last name starting with "T".

Adding a Content Source means you're telling GoLive: "please look in table 'x' of database 'y', and pull out all records that match my filter requirements so I can have that data available as content for this page." To add a Content Source, you just drag and drop a Content Source object from the Dynamic Content palette in the head section of a page.

Identify your Data Source

Now you need to tell GoLive what Data Source your site will use. If you have an Access database, you could just take your .mdb file and move it into the config > datasources folder, and you'd be done. If your .mdb file is password-protected, however, you'll want to use the procedure that follows.

If you're using a password protected Access database or MySQL, it's not hard to create a Data Source. Just go to the Site menu and choose Settings > Dynamic Content. In the Data Sources area click New. Then choose the type of Data Source you'll use (e.g. ADO Database, MySQL database, etc.). In the Data Source editor, give the Data Source a name first. The Data Source does not need to be given the same name as the database file itself, but it may be easier to name it the same way in order to avoid confusion. GoLive will add the proper file extension to the name you provide.

Next, give it the URL where the database exists on your Web server, and tell it the name of the database itself. You also need to tell it the username and password needed to access the database. That's it! If you have any questions here, pages 382-385 of the GoLive 6.0 manual can answer them.

Step 5. Search and You Shall Find

You've created your database, made your site dynamic, and linked the two together. You're finished with the groundwork, so it's time for some fun!

You'll now create what is essentially a dynamic Search form that can pull up your data and display it based on keyword searches. Doing this even once will open up a new realm of ideas for your site - it means you know the basics of some cool tricks with site forms, and can display user data in interesting new ways.

A Dynamic Search Page

Make your Search page dynamic by opening the static search page you mocked up in Step 1. Open the Dynamic Bindings palette and choose the server scripting language you want (ASP, PHP, JSP) and click Convert Page. GoLive automatically renames the page's extension to match what you selected.

From here you can add one or more Content Sources (the queries, remember?) to the head section. To get content, drag a Content Source object from the Dynamic Content palette to the head section of your Search page. Fill in the Inspector fields as follows:

• Description: "Search_Results" (name it what you want, but this Content Source will hold our search results)
• Type: ADO Database Query (or MySQL Database Query if using PHP/MySQL)
• Choose the database name from the pop up menu (adobe, if using the example database)
• Choose the table name from the pop up menu (golive_test, if using the example database)

Filter the Searches

At this point, the Search_Results Content Source will return ALL records from the database table we specified. But... what if we don't want all records? You can now filter the Search_Results Content Source so it will only return records based on a user's search request. GoLive provides a very nice visual way to do this via the Content Source Inspector. To get started:

1. Link the search Form Action on your Search page to the file itself (index.asp or the name you gave it)

2. Make sure the Form Method is "Get", not "Post"

3. Rename your text field on your search form to "lname" (or, the same name as the last name field of the sample database)

4. Select "lname" from the Field Name list in the Content Source Inspector

5. Choose Match request parameter 'lname' from the Match Pattern popup menu to the right in the Content Source Inspector

6. Bind the table you created to display search results to the Search_Results Content Source using the Dynamic Bindings palette by:

7. Selecting the table

8. Clicking Replace Rows in the Dynamic Bindings palette

9. Choosing Search_Results as the Content Source

10. Setting it to display 5 records per page (this is for Block Navigation; more on this later)

11. Bind the fields in the first row of the table to their appropriate fields in the database using the Dynamic Bindings palette. You only need to worry about the first row! The rest of the table will fill in automatically, because you checked "Replace Rows" in the Dynamic Bindings palette. You will bind the fields by selecting the mock content first name text and binding it to the first name field in the Dynamic Bindings palette. Do the same for the last name and phone number text in the first row of the table, binding them to their respective fields in the database.

What we've just done is tell our Content Source to kick back all the records whose last name field exactly matches what a user typed into your search form text field.

Step 6. Fine Tuning the Searches

Your dynamic Search form is working, but now you need to plan for all the ways a user might try to use it. For instance, what if the user calls the page directly, or enters nothing for search criteria? At this point your Search page is looking for a "request parameter", and it has to match the "lname" exactly. GoLive has given us a great starting place, and from here a few additional steps will cover the contingenes:

1. Rename your text field on your search form to "keyword".

2. Change the Table pop up menu to Custom SQL in the Content Source Inspector

3. Select the text in the SQL edit box and delete it.

4. Type or paste the text below into the SQL box:

   For ASP:

   select * from golive_test where lname like    
'%{Request.QueryString("keyword")}%' or fname like    
'%{Request.QueryString("keyword")}%' or phone like    
'%{Request.QueryString("keyword")}%'

   For PHP:

   select * from golive_test where lname like    
'%{@$GLOBALS["keyword"]}%' or fname like '%{@$GLOBALS["keyword"]}%'    
or phone like '%{@$GLOBALS["keyword"]}%'

   This snippet of source code transforms the Search_Results Content Source into a practical search engine. The above code basically says: "look at what the user typed into the search field and see if it matches anything in any of the fields of all the records. And while you're at it, return the matching records and make them available as content for this page." This is what we want! If nothing is typed into the search box, or if the page is called directly with no previous page, ALL records are returned. Anything typed into the search box will be matched to the database fields.

Note that the "select * from golive_test" statement means to select everything in the database named 'golive_test'. The 'where' statement sets you up to make some qualifications. The 'like' word specifies a partial match, so it doesn't have to be exact. The '%' symbol is a wildcard that tells it to match any number of characters. Because there are '%' symbols on both ends of the expression, the search will match every record if nothing is typed into the search box or if the page is called first with no previous page. It's kind of like asking "show me records that contain any number of characters + what the user typed + any number of characters". That sounds like word math to me! If the user typed nothing, or if the page is called first, the word math for that would be: Records = 'any number of characters' + 0 + 'any number of characters'. Hey... every record qualifies for that!

Block Navigation

Now you want to add some Block Navigation, which is a cool way to deliver users their search results. Block Navigation lets you specify how many records per page you want to display, and includes links that allow the user to browse easily through multiple pages of results.

Start by adding another blank Content Source to the header of your page. Fill out the fields as follows:

1. Description: Block (again, name this anything you want, but this Content Source will provide our Block Navigation)

2. Type: Navigation Block View

3. Content Source: "Search_Results" (or whatever you named that first Content Source.)

4. Create a regular 1 row x 2 cell table underneath the search results table - in the first cell type "Records:"

5. Drag a Repeat Cells object from the Dynamic Content palette into the second cell of the 1x2 table

6. Delete all but one cell of the Repeat Cells table and type "x-y" in that cell

7. Select the 'x' and bind it to the "Block" Content Source with the filed set to "First"

8. Select the 'y' and bind it to the "Block" Content Source with the filed set to "Last"

9. Select the whole 'x-y' text and link it to your Search page

10. In the Dynamic Bindings palette Click "Bind Link Destination To" and set the Field to "Link_URL"

What you now have is a page that is looking for searches and doing block navigation. When someone types in a keyword like "John," all the records that contain "John" in the fields you indicated will be displayed. It also will use the "Block" Content Source to provide those handy links that allow a user to bounce between multiple pages of results. This is Block Navigation in the form "Records: 1-5 6-10 11-13, etc." Very cool! And you thought you couldn't program!

Tip: Remember to set your posting method for your search form to "Get" as "Post" isn't compatible with Blockavigation.

At this point you can use the GoLive Preview Tab to see results, but note that your page is not live, so you really are only previewing. If you get an error message when trying to display a custom search, try previewing it in a browser. Also, GoLive 6.0.1 displays this custom code correctly in Preview mode, so ensure you've done the upgrade.

Step 7. Allowing Record Ownership

The last thing you need to do on the Search page is create a login area at the bottom. This is where users can enter their last name and password in order to get to the Edit page. Here they can update the information that you will have bound to fields on that page. Their changes will dynamically update the database.

On the Edit page (edit.html), the process is straightforward:

1. Create a form that displays the text fields you want users to update, label the text fields accordingly.

2. Bind the form fields to their corresponding database fields using the Dynamic Bindings palette.

3. Copy and paste the Search_Results Content Source from the Search page to the head section of the Edit page

4. Rename the Edit page Content Source to Matched_Record

5. Set the lname field to "Match request parameter 'lname'" in the Filter Records section of the Inspector

6. Set the password field to "Match request parameter 'password'" in the Filter Records section of the Inspector

7. Add a Submit Action button from the Dynamic Content palette

8. Make sure the Action is set to Update Record in the Dynamic Bindings palette

9. Set the Content Source to Matched_Record

Tip: At this point you could also create a generic success and failure page that says something like "Action Successful" and "Operation Failed!" You could then set the "On success redirect to:" and "On failure redirect to:" options in the Dynamic Bindings palette to link to the appropriate pages.

So now you've created a Content Source that only returns records that match the Last Name and Password exactly. Guess what? That means only one record will be returned, and only if the user typed in the exact last name and matching password. You've just implemented a simple form of Record Ownership!

What if someone enters an incorrect name and password? Just set up your Edit page to "Hide Content" if no matching record is found, as follows:

1. Drag and drop a Hide Content object from the Dynamic Content palette onto the Edit page

2. Cut and paste your edit form into the Hide Content area next to the word "Content" (you can then delete the word 'Content')

3. Select the Hide Content object

4. Click Show/Hide in the Dynamic Bindings palette and select Hide

5. Choose "If no records" from the pop up menu

6. Select Matched_Record as the Content Source

What you've done is say: "please hide this form only if there was no match; i.e. the user typed in an incorrect name and password."

And, as we don't want them to get a blank page if their login fails:

1. Drag and drop a Hide Content object from the Dynamic Content palette onto the Edit page

2. Cut and paste your login form from the Search page into the Hide Content area next to the word "Content" (you can then delete the word 'Content')

3. Type "Try Again!" above the login form

4. Select the Hide Content object

5. Click Show/Hide in the Dynamic Bindings palette and select Show

6. Choose "If no records" from the pop up menu

7. Select Matched_Record as the Content Source

This last step enables the user to try again.

I also use this technique on the Search page to show and hide content in case there is a search that returns no records. The sample Search page will show you how it's done.

This tutorial shows enough for you to figure out the other features implemented in the sample files. If you look through those you can see examples of:

• Deleting a Record
• Confirming a record deletion
• Adding a Record by generating a blank form
• Adding a Master Admin page for Admin editing of ALL records

Putting It All Together

Today's Web publishing tools make it possible for relatively non-technical designers to build and deploy database-driven Websites. As demonstrated above, Adobe GoLive palettes and wizards can give your site a significant leap in functionality and effectiveness without much programming expertise.

As you experiment with a basic level of dynamic data, you will quickly begin to devise ways to make your sites more streamlined and efficient. From there, the sky's the limit!

Thanks go to David Richards of WDTP for his assistance with this tutorial.

[1] http://www.adobe.com/products/golive/main.html
[2] http://www.webmasterbase.com/examples/golivedynamic/samplesites.zip