MS FrontPage

Working with Databases

Imagine that you're running an online sock store. You'd like to show your customers all the varieties of footwear that you offer, to increase the chance of making a sale. Sure, you could create an HTML page to profile each and every kind of sock you offer. But that's a lot of work. Plus, all that information already lives in your sock database, including pictures of each style. How can you get that information onto your Web pages?

If you want your Web pages to display information from a database, you'll need to understand the building blocks that can make that magic happen. Read on to learn about database basics and dynamic Web pagespages that get generated on the fly, as your visitors request them. Then find out how it's possible for these technologies to work together. After you read about what software you need, you can make sure you have all the necessary tools at your disposal. FrontPage needs extra support from the Web server to orchestrate this communications feat.

What's a Database?

Everywhere you gofrom offices to shops to homespeople need to store information and do so in an organized manner, so they can retrieve it easily. Take your doctor's office, for instance. You stop in for a visit and the receptionist looks through stacks of file folders for your records, which are stored together in a manila folder.

You can think of a database as the digital equivalent of those file folder stacks. A database is made up of tables, whichlike the folders in your doctor's officegroup records that contain similar information. One table of a database might contain a list of products. Another table could track vendor names and contact information, and yet another might hold customer records. To see what one such table looks like, you can open the sample database that comes with FrontPage (see Figure 16-2). To locate the Northwind sample database, look in C:\Program Files\ Microsoft Office\Office\Samples or search your C:\ drive for the file Northwind.mdb.

Figure 16-2. This table from the Northwind sample database shows employee data. Each row contains one person's information. In other words, each row is one record. Each column stores a single type of information: Last Name, First Name, and so on. When you create a form (Forms and Databases), each field corresponds to a table column.

If your database has just one table (which makes it a flat-file database), life will be blissfully simple. But most databases are more complex and contain many tables. Many of these tables are linked to each other by at least one field, creating what's known as a relational database. For example, the Northwind database includes a Products table, which lists all the products the company offers. Each product record contains information fields like Product Name, Price, and Supplier. The Supplier field is different from these other fields, because you don't just type in the supplier name, you get it from another tablethe Suppliers table. The value in this field matches up with one record in the Suppliers table, which includes additional details like supplier location and contact information that you wouldn't want cluttering up your Products table. A simplified example of how this relationship works is illustrated in Figure 16-3.

Each database table has one column called the primary key, which the database program auto numbers to ensure that each record has one unique identifier. In Figure 16-3, the ArtistID and WorkID columns carry out this duty in each table.

Dynamic Web Pages

To take data directly from a database and display it on a Web page, you'll need more than plain old HTML. Sure, you need some HTML, so the browser knows how to display page elements, but you also need to throw in some serious scripting.

Think about the last time you shopped for books online. Say you're at your favorite bookseller's site and you enter in some search words like "Roald Dahl." The site presents you with a list of matches. Next, you enter the same search term again but add the word "Charlie" to it. You get a similar list of matches, but this time "Charlie and the Chocolate Factory" is on top.

Figure 16-3. How do you know which of the artists from the Artists table (at top) created the pieces in the ArtWorks table (at bottom)? You can solve the puzzle by matching up numbers in the ArtistID field. This kind of link between tables is a basic attribute of relational databases.

Did the site's creator anticipate your search terms and have, ready-and-waiting, two separate results pages? And what happens next when you click a link for product details? Did your bookseller create a single HTML page for each of the thousands of products on her site? No way. On a large shopping site, all the information you see when you conduct a search or click a product details link comes out of a database. It takes a special kind of Web page to take your request, use it to query (search) a database, and then produce an entirely new HTML page to display information from the database. (Now do you understand why these pages are known as dynamic Web pages?) Actually, all this activity isn't the work of the page alone; the process involves a program on the server, which works together with the dynamic Web page. These special pages come in a variety of flavors, but the most common are Microsoft's ASP (Active Server Pages).

ASP

Active Server Pages are one of the easiest and most popular building blocks you can use to create a database-driven Web site. ASP pages are dynamic, not static, like a regular HTML page. Put it another way: you use FrontPage to manually create static pages; dynamic pages get summoned into existence each time your visitors search for a word or phrase, or click a particular link.

Here's how Active Server Pages work. A browser requests an ASP page. The ASP engine on the Web server reads the ASP page and executes scripts the page contains. Then the server sends the results of that process back to the browser where it displays as an HTML page.

Because scripts are processed on the Web server, out of sight, ASP provides a lot more security for your code than a regular HTML page would.

ASP.NET

ASP.NET (pronounced a-s-p dot net) is an upgrade to ASP. .NET is a more complex and cleaner variation on ASP, and it eventually will replace its predecessor. If you're deciding which technology you'd like to use, you may not have much of a choice. Some Web hosts offer only one or the other. If you're creating Web pages for a company, your Web site administrator may already have made the choice for you.

Other server-side scripting

So now you need to become an ASP programmer, right? Naw, don't worry. As you'll see, FrontPage creates your ASP or ASP.NET pages for you. However, if you want to use other scripting options like CGI (Common Gateway Interface) or PHP (Hypertext Preprocessor) to interact with your database, you'll need to be an expert or hire one.

Database Connections

So where's your database going to live? FrontPage can handle a few scenarios.

If you're working with a simple Access database that's not too big, you can store your database right inside your Web site. Other more complex databases are too large for that kind of setup. In some cases, you'll connect to a database that lives on a separate server altogether. Many databases must reside on what's known as a database server and require the assistance of an application server to act as middle man between your Web page and the data. Or your database and data-handling application can all live on one computer that's sophisticated enough to tackle all these jobs.

FrontPage can work with pretty much any kind of database. Not surprisingly, things work more smoothly if you use Microsoft Access or Microsoft SQL. If visitors are going to view your database frequentlysay over a few thousand times a daythen you'll want to opt for SQL, the more robust of the two. However, if your database is small and won't experience that much traffic, Access will perform fine. If you're not a complete devotee of Microsoft products, don't worry, you can use other types of databases, too. All FrontPage needs is a way to connect with the database.

Establishing this connection is a breeze if you're linking to a simple database you've saved inside your Web site. But FrontPage can also connect to databases that live in other locations that at first might not seem compatible with FrontPage.

Thanks to a protocol called ODBC (Open Database Connectivity), FrontPage can connect to pretty much any kind of database you want. ODBC acts as a middle man or translator that lets a variety of programs (including FrontPage) communicate with all sorts of database types, even those made by another manufacturer like Oracle. As long as your database is ODBC-compliant, FrontPage can connect to it.

What You'll Need

You can cook up all this database magic on a disk-based site and upload everything to a Web server that's loaded with FrontPage Server Extensions. (FPSE are required for all the features covered in this tutorial, except the Database Results Wizard. However, since the extensions help you publish and manage this feature, you should consider it a requirement.) However, if you're working extensively with data, you'll be much happier developing in a Web server environment. That way, you can test and manipulate the way your data displays as you work. For instance, if you develop on a disk-based site, you won't be able to preview an ASP page until you publish it. Dynamic Web pages need to live on a Web server since that's where the required server software (also called Active Server Pages) exists, making actual database-searching possible. Your Web server should have the latest version of FPSE as well as Active Server Pages (ASP) installed. (Pop back to Where to Create Your Web Site to read about the difference between disk-based and server-based development environments.)

One easy way to create a Web serverbased development environment is to install Microsoft Internet Information Services on your machine, if possible. Then download and install FPSE 2002, and you'll have all the capabilities that you need. (See Using Site Templates and Wizards for more information on turning your PC into a Web server.)

FrontPage can help you get a simple database-driven site up pretty quickly. However, if, after reading about your options, you find that your needs are more complex, you'll need to look for more assistance and maybe even hire someone to help you.

by BrainBellupdated
Advertisement: