Understanding Database-Driven Sites

---

Until now, your site pages have been static — the content in them is hard-coded into each page's HTML structure. You may have used client-side logic (JavaScript) to add some interactivity, such as rollovers and DHTML elements, but for the most part, what the site visitor sees is the same as what you put into each page. The visitor types in the URL or clicks a link to request the page from a Web server. The Web server receives the request and sends the HTML to the visitor's browser, where it is processed and displayed. This sort of page generally uses an .htm or .html extension because that is what it consists of — HTML.

A dynamic page is different; its content is partially undefined and is either drawn from a data source after the Web server receives the request for the page or is generated through stored variables or procedures. Content sources can include a column in a recordset, a value submitted by an HTML form, the value contained in a server object, or even data stored in a session variable. In all cases, special instructions for generating or processing the required content are included in the page along with the HTML. Dynamic pages use different extensions (.cfm, .asp, .shtml, and so on) to let the server know that processing is required. That processing is done by an application server — special software that handles the server logic included in tags inserted into the HTML of the page. The Web server passes the page to the application server that is designed to scan the page for the instructions, which are carried out. The application server then returns the page as HTML with the added content.

Often the required content is drawn from a database. A database is a collection of tables that use columns and rows to build relational data sets or records. The instruction to extract data from a database is a query and the language that is used in a query is Structured Query Language (SQL). Application servers and databases cannot communicate with each other directly; software (a database driver) is necessary to act as an interpreter. Through a connection made with the driver, a subset of the information stored in the database (a recordset, resultset, or dataset depending upon the technology used) is extracted via the query for use in the requested page. The recordset, resultset, or dataset is returned to the application server and the data is merged into the HTML page, which is returned to the Web browser as HTML only, as shown in Figure 46-1.

Figure 46-1: Dynamic, data-driven pages must be processed by the application server and a recordset extracted against a database via a query passed back and forth through a database driver.

Note 

For examples (and CD exercises) in this chapter, a recordset is used because the technology chosen is ASP/VBScript.

Choosing a dynamic content source

There are many sources of dynamic content. In Dreamweaver, a data source can be request, form or server variables, stored procedures, a database, or a combination of these. The data source you use depends on what you need to accomplish. For example, a database is practically a requirement if you want to store collected data on a permanent basis. All other data sources are temporarily stored in the memory of the server, or on the user's computer as a cookie — a small set of data stored in the user preferences of the Web browser. Cookies are generally given an expiration date and because there are limits to the total number of cookies maintained by the browser, a cookie can be displaced by other cookies, thus destroying the stored data.

Understanding databases

A database is one or more groups of information (tables) that contain entry records (rows) that store a specific type of information (columns). For each table, it's likely that the columns are related. For example, a "contacts" table's columns might include names, addresses, phone numbers, e-mail addresses and the like — bits of separate data that together provide the information needed to reach that person. Generally, one column is set to be a primary key — a unique identifier — that may be used when inserting or editing records. In the case of a contacts database, trying to use only one column, like a last name, to identify a user is problematic; it's possible that there is more than one person listed with the same last name. In this type of case, the database developer would have the database autogenerate a number to identify each person and that would serve as the primary key. On the other hand, if the data is such that one column is unique — say a social security number or driver's license number — the database developer may opt to use that unique number as the primary key.

Primary keys may be used to compare data across tables. Special relationships between a column of one table may be made with column(s) in other tables (one-to-one or one-to-many). For example, a social security number might be the primary key for one table and could be equated to the customer ID of another table, which in turn might be equal to the account number in yet another table. Such relationships may be defined in the database itself, or created when certain columns are requested where specific operators (such as if, when, where, and, or) are used to compare the columns (as in =, <, >, etc.). Dreamweaver provides tools to locate data where one column compares to another by building queries to generate recordsets.

Where does the database come from?

The data management choice you make depends on your computer platform and/or a combination of Web server support, server technology, resources, and budget. Getting a database set up is often the hardest hurdle to overcome for new developers, from deciding what type of database to use to building the database tables.

Tip 

Dreamweaver has nothing to do with building a database, but a useful resource for users new to database logic can be found in the help files for Dreamweaver. To access the help files, choose Help ® Using Dreamweaver and in the Contents category, click open the Appendixes section to locate the "Beginner's Guide to Databases."

Databases come in many forms and some solutions are more robust than others. For example, a commonly used solution is a Microsoft Access database. Easy to install and use on most Windows computers, Access provides a solution that is affordable, simple and adequate for most sites, but it does have some limitations in overall size and number of concurrent users (2GB/255 users). More robust solutions include Microsoft SQL Server, DB2 (IBM) or Oracle9i, but these solutions are also quite expensive. Many Unix, Linux, and Mac OSX users opt for MySQL (a popular open-source database system).

Tip 

Whether your database and development is locally or remotely tested, the proper permissions and access to the database folder must be set up. Users accessing the database via your pages must be able to read, write, and execute the functions set up by the logic of each page. Typically, Windows-based solutions allow permissions and network access for folders via the Properties dialog box, as shown in Figure 46-2.

Figure 46-2: Right-click to get the Properties of the database folder and select the Web Sharing tab to share the folder. Create an alias for the folder. Use the Edit Properties button to set up READ and EXECTUTE permissions.

On the CD-ROM 

The database used in the exercise files (admin.mdb) is an Access 2000 database. It is located in the Chapter 46\Admin_Site\database folder.

---