- Introduction
- Bootstrap Content
- Storing Your Bootstrap Content
- From Database to Web Page: Four Easy Steps
- Homework Assignment 6: Designing a Bootstrap
- Glossary of Business and Technical Terms
From Database to Web Page: Four Easy Steps
These are the general steps you take to retrieve information from a database table and display it in a Web page:
-
Open the database.
-
Send an SQL command to the database to retrieve one or more records from a table(s) in the database.
-
Display one or more fields from the records in a Web page.
- Close the database.
However, you can't take these steps in a plain-old HTML file. You need to combine HTML with a programming language, and this is where Active Server Pages (or ASP for short) comes in.
An ASP file is simply a text file, just like your HTML files are text files; the file extension for an ASP file is .asp versus .html or .htm for an HTML file. The key difference between an ASP file and an HTML file is that the typical ASP file contains both HTML and Visual Basic code. Whereas HTML "code" is delimited by angle brackets—otherwise known as "greater-than" and "less-than" signs—(< and >), Visual Basic code in an ASP file is delimited by the symbols <% and %> (commonly verbalized as "begin-percent" and "end-percent"). Once you create an ASP file, you upload it to the same Web server directory where you put your HTML files.
The best way to understand how to use ASP files to retrieve bootstrap content out of a database table and display it as a Web page is through an actual example. However, before doing this example, let's review the problem again, since there are so many different pieces to the solution—HTML, databases, Active Server Pages—that it's easy to forget exactly what problem we're trying to solve. Once you're comfortable with your understanding of both the problem and the solutions, it's a relatively straightforward matter to generalize the solution to bootstrap content on your own personal Web business site.
Review of the Problem
We have a database (hacker.mdb) and a table inside that database (hackerphrase) that contains about 50 hacker phrases. How do we get that information out of the database and onto a Web page so that a user can view it? First, let's look at the code (see Listing 1) that generates the home page for our hacker phrase site (refer to Figure 2).
){kind=link}
Listing 1 The Script Without Active Server Pages Code
<HTML>
<HEAD>
<TITLE>Professor F's IT-Forum</TITLE>
<STYLE>
<!--
BODY {font-family:verdana,arial,helvetica;font-size:9pt}
TD {font-family:verdana,arial,helvetica;font-size:9pt}
A {color: #572119; font-weight: bold}
-->
</STYLE>
</HEAD>
<BODY>
<TABLE WIDTH=600 HEIGHT=400 CELLPADDING=0 CELLSPACING=0 BORDER=0>
<TR>
<TD WIDTH=120 VALIGN=TOP BGCOLOR=#DFCCAC>
<img src=logo.gif><BR><BR>
<TABLE WIDTH=100%><TR><TD BGCOLOR=#572119 ALIGN=CENTER>
<FONT COLOR=#DFCCAC><B>WORDAGE</B></FONT>
</TD></TR></TABLE>
<A HREF=" ">Phrases</A><BR>
<A HREF=" ">Dictionary</A><BR>
<A HREF=" ">Archive</A><BR><BR>
<TABLE WIDTH=100%><TR><TD BGCOLOR=#572119 ALIGN=CENTER>
<FONT COLOR=#DFCCAC><B>CULTURE</B></FONT>
</TD></TR></TABLE>
<A HREF=" ">History</A><BR>
<A HREF=" ">Definition</A><BR><BR>
<TABLE WIDTH=100%><TR><TD BGCOLOR=#572119 ALIGN=CENTER>
<FONT COLOR=#DFCCAC><B>DISCUSSION</B></FONT>
</TD></TR></TABLE>
<A HREF=" ">Forum</A><BR>
<A HREF=" ">Chat</A>
</TD>
<TD WIDTH=480 VALIGN=TOP>
<IMG src=informitad1.gif>
<TABLE WIDTH=100% CELLPADDING=1 CELLSPACING=0 BORDER=0 HEIGHT=340>
<TR>
<TD WIDTH=360 VALIGN=TOP><BR>
<CENTER><FONT STYLE="font-size:16pt" COLOR=#572119><B>
Professor F's IT-Forum</B>
</FONT></CENTER>
<HR COLOR=#572119>
<TABLE CELLSPACING=0 CELLPADDING=0 BORDER=0><TR>
<TD BGCOLOR=LIGHTGREY>
Welcome to <B>Professor F . com</B>--a repository of hacker
culture and a forum for discussing I.T. issues. Our mission is
to correct the negative, misportrayal of the hacker by the
media. So, you won't find anything about breaking into
computers on this site. Instead, you'll find something much
more valuable--the truth. Enjoy!
</TD></TR></TABLE>
<HR COLOR=#572119><BR>
<TABLE>
<TR><TD VALIGN=TOP>Phrase</TD>
<TD><B>Sure, you're bitter</B></TD></TR>
<TR><TD VALIGN=TOP>Usage</TD>
<TD>Whenever a friend or coworker complains too
much.</TD></TR>
<TR><TD VALIGN=TOP>Example</TD>
<TD>Barney: I spend all night hacking this algorithm and
I still can't get it to work<BR>
Fred: Sure, you're bitter. </TD></TR>
</TABLE>
</TD>
<TD WIDTH=120 VALIGN=TOP>
<CENTER><FONT STYLE="font-size:12pt" COLOR=#572119><B>
Top 5<BR>Phrases
</CENTER></B></FONT>
<TABLE WIDTH=100% CELLPADDING=0 CELLSPACING=0 BORDER=1
BGCOLOR=#DFCCAC BORDERCOLOR=#572119>
<TR><TD>
<TABLE>
<TR><TD VALIGN=TOP>1.</TD><TD>Sure, you're bitter </TD></TR>
<TR><TD VALIGN=TOP>2.</TD><TD>Any other questions? </TD></TR>
<TR><TD VALIGN=TOP>3.</TD><TD>Help me Spock! </TD></TR>
<TR><TD VALIGN=TOP>4.</TD><TD>Are you threatening me?</TD></TR>
<TR><TD VALIGN=TOP>5.</TD><TD>Did you just get in? </TD></TR>
</TABLE>
</TD></TR></TABLE>
<BR>
<CENTER><FONT STYLE="font-size:12pt" COLOR=#572119><B>
Top 5<BR>Words
</CENTER></B></FONT>
<TABLE WIDTH=100% CELLPADDING=0 CELLSPACING=0 BORDER=1
BGCOLOR=#DFCCAC BORDERCOLOR=#572119>
<TR><TD>
1. Foo<BR>
2. Bar<BR>
3. Baz<BR>
4. Frob<BR>
5. Grok
</TD></TR></TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
We need to replace the text highlighted in bold above with a phrase from our hackerphrase table. To solve this problem, we'll apply the four steps listed at the beginning of this section.
Step 1: Opening the Database in Active Server Pages
Opening a database in Active Server Pages (ASP) requires just two lines of code (see Listing 2). These two lines of code are the same for every ASP file you create for accessing a database. In the first line of code, you create a database object; for example, conn. Think of this database object as an intermediary between your ASP code and your database; that is, all communication with your database will take place via this database object. The database object needs to know which database to talk to, as you might have several databases on your Web site. So, in the second line of code (which I've had to split up into several lines for formatting reasons), you tell the database object which specific database to open—for this example, hacker.mdb.
Listing 2 Active Server Page Code to Open a Microsoft Access Database Called hacker.mdb
<%
'
' Line 1: Create the database object
'
set conn=server.createobject("ADODB.Connection")
'
' Line 2: Open a particular database (hacker.mdb)
'
conn.open("DBQ=" & server.mappath("hacker.mdb") & ";" & _
"Driver={Microsoft Access Driver (*.mdb)};" )
%>
... remainder of listing same as Listing 1 ...
Now that our database is open, we can command it to retrieve content out of its tables. In the next section we'll write some code to retrieve a random hacker phrase out of the hackerphrase table.
Step 2: Send an SQL Command to Retrieve Records from a Table
Given a table that has records numbered from 1 to n, to randomly select a record you take the following steps:
-
Count how many records are in the table.
-
Select a particular record at random.
-
Send a query to the database to retrieve that record.
To count how many records are in a table, you use the SQL SELECT query:
SELECT COUNT(*) AS variable FROM table
For example,
SELECT COUNT(*) AS np FROM hackerphrase
You then send this command to the database, using the database object's execute method (see Listing 3).
Listing 3 Code Fragment That Counts Records in a Table
set rs=conn.execute("SELECT COUNT(*) AS np FROM hackerphrase")
nphrases=rs("np")
We store the record returned by the query in a variable (arbitrarily labeled rs in this case). We then extract the total number of hacker phrases (np) from this record and store it in a variable, nphrases. Now that we know how many phrases are stored in the table, we can proceed to the second step of choosing a phrase at random. To accomplish this step, we use the built-in Visual Basic random functions (randomize and rnd) to help pick a number between 1 and the nphrases (see Listing 4):
Listing 4 Code Fragment to Randomly Choose a Phrase from the Table
randomize n=CINT(rnd*nphrases+1)
Finally, we use another SQL SELECT query to grab that particular record (n):
SELECT * FROM table WHERE condition
For example,
SELECT * FROM hackerphrase WHERE phraseID=n
Again, you send this command to the database using the database object's execute method (see Listing 5):
Listing 5 Code Fragment to Retrieve the Randomly Selected Phrase
set rs=conn.execute("SELECT * FROM hackerphrase WHERE phraseID=" & n)
If we combine all these fragments, the code looks like Listing 6:
Listing 6 Combining Code Fragments to Retrieve a Random Record from the Database
<%
'
' Line 1: Create the database object
'
set conn=server.createobject("ADODB.Connection")
'
' Line 2: Open a particular database (hacker.mdb)
'
conn.open("DBQ=" & server.mappath("hacker.mdb") & ";" & _
"Driver={Microsoft Access Driver (*.mdb)};" )
'
' Listing 3: Code fragment to count records in a table
'
set rs=conn.execute("SELECT COUNT(*) AS np FROM hackerphrase")
nphrases=rs("np")
'
' Listing 4: Code fragment to randomly choose a phrase from the table
'
randomize
n=CINT(rnd*nphrases+1)
'
' Listing 5: Code fragment to retrieve the randomly selected phrase
'
set rs=conn.execute("SELECT * FROM hackerphrase WHERE phraseID=" & n)
%>
... remainder of listing same as Listing 1 ...
Next, we'll display the hacker phrase in an HTML page. Refer to the bold items in Listing 1; what we want to do is substitute the phrase that we randomly selected from the database for those "hard-coded" bold items . The variable rs contains the record corresponding to that phrase. To replace the hard-coded phrases with fields from our randomly selected record, we use this syntax:
<%=recordvariable("fieldname")%>
Specifically, instead of the hard-coded hacker phrase (see Listing 7), you substitute the randomly selected hacker phrases (see Listing 8):
Listing 7 The Hard-Coded Hacker Phrase (Fragment of Listing 2)
...
<TABLE>
<TR><TD VALIGN=TOP>Phrase</TD>
<TD><B>Sure, you're bitter</B></TD></TR>
<TR><TD VALIGN=TOP>Usage</TD>
<TD>Whenever a friend or coworker complains too
much.</TD></TR>
<TR><TD VALIGN=TOP>Example</TD>
<TD>Barney: I spend all night hacking this algorithm and
I still can't get it to work<BR>
Fred: Sure, you're bitter. </TD></TR>
</TABLE>
...
Listing 8 The Randomly Selected Hacker Phrase in Place
...
<TABLE>
<TR><TD VALIGN=TOP>Phrase</TD>
<TD><B><%=rs("phrase")%> </B></TD></TR>
<TR><TD VALIGN=TOP>Usage</TD>
<TD><%=rs("usage")%>
</TD></TR>
<TR><TD VALIGN=TOP>Example</TD>
<TD><%=rs("example")%>
</TD></TR>
</TABLE>
...
You then close the database (see the end of Listing 8 above). Listing 9 shows the final code, with ASP code highlighted.
Listing 9 Final ASP file to Randomly Select a Hacker Phrase from a Database
<%
set conn=server.createobject("ADODB.Connection")
conn.open("DBQ=" & server.mappath("hacker.mdb") & ";" & _
set rs=conn.execute("SELECT COUNT(*) AS np FROM hackerphrase")
nphrases=rs("np")
randomize
n=CINT(rnd*nphrases+1)
set rs=conn.execute("SELECT * FROM hackerphrase WHERE phraseID=" & n)
%>
<!--
1234567890123456789012345678901234567890123456789012345678901234567890
-->
<HTML>
<HEAD>
<TITLE>Professor F's IT-Forum</TITLE>
<STYLE>
<!--
BODY {font-family:verdana,arial,helvetica;font-size:9pt}
TD {font-family:verdana,arial,helvetica;font-size:9pt}
A {color: #572119; font-weight: bold}
-->
</STYLE>
</HEAD>
<BODY>
<TABLE WIDTH=600 HEIGHT=400 CELLPADDING=0 CELLSPACING=0 BORDER=0>
<TR>
<TD WIDTH=120 VALIGN=TOP BGCOLOR=#DFCCAC>
<img src=logo.gif><BR><BR>
<TABLE WIDTH=100%><TR><TD BGCOLOR=#572119 ALIGN=CENTER>
<FONT COLOR=#DFCCAC><B>WORDAGE</B></FONT>
</TD></TR></TABLE>
<A HREF=" ">Phrases</A><BR>
<A HREF=" ">Dictionary</A><BR>
<A HREF=" ">Archive</A><BR><BR>
<TABLE WIDTH=100%><TR><TD BGCOLOR=#572119 ALIGN=CENTER>
<FONT COLOR=#DFCCAC><B>CULTURE</B></FONT>
</TD></TR></TABLE>
<A HREF=" ">History</A><BR>
<A HREF=" ">Definition</A><BR><BR>
<TABLE WIDTH=100%><TR><TD BGCOLOR=#572119 ALIGN=CENTER>
<FONT COLOR=#DFCCAC><B>DISCUSSION</B></FONT>
</TD></TR></TABLE>
<A HREF=" ">Forum</A><BR>
<A HREF=" ">Chat</A>
</TD>
<TD WIDTH=480 VALIGN=TOP>
<IMG src=informitad1.gif>
<TABLE WIDTH=100% CELLPADDING=1 CELLSPACING=0 BORDER=0 HEIGHT=340>
<TR>
<TD WIDTH=360 VALIGN=TOP><BR>
<CENTER><FONT STYLE="font-size:16pt" COLOR=#572119><B>
Professor F's IT-Forum</B>
</FONT></CENTER>
<HR COLOR=#572119>
<TABLE CELLSPACING=0 CELLPADDING=0 BORDER=0><TR>
<TD BGCOLOR=LIGHTGREY>
Welcome to <B>Professor F . com</B>--a repository of hacker
culture and a forum for discussing I.T. issues. Our mission is
to correct the negative, misportrayal of the hacker by the
media. So, you won't find anything about breaking into
computers on this site. Instead, you'll find something much
more valuable--the truth. Enjoy!
</TD></TR></TABLE>
<HR COLOR=#572119><BR>
<TABLE>
<TR><TD VALIGN=TOP>Phrase</TD>
<TD><B><%=rs("phrase")%> </B></TD></TR>
<TR><TD VALIGN=TOP>Usage</TD>
<TD><%=rs("usage")%>
</TD></TR>
<TR><TD VALIGN=TOP>Example</TD>
<TD><%=rs("example")%>
</TD></TR>
</TABLE>
</TD>
<TD WIDTH=120 VALIGN=TOP>
<CENTER><FONT STYLE="font-size:12pt" COLOR=#572119><B>
Top 5<BR>Phrases
</CENTER></B></FONT>
<TABLE WIDTH=100% CELLPADDING=0 CELLSPACING=0 BORDER=1
BGCOLOR=#DFCCAC BORDERCOLOR=#572119>
<TR><TD>
<TABLE>
<TR><TD VALIGN=TOP>1.</TD><TD>Sure, you're bitter </TD></TR>
<TR><TD VALIGN=TOP>2.</TD><TD>Any other questions? </TD></TR>
<TR><TD VALIGN=TOP>3.</TD><TD>Help me Spock! </TD></TR>
<TR><TD VALIGN=TOP>4.</TD><TD>Are you threatening me?</TD></TR>
<TR><TD VALIGN=TOP>5.</TD><TD>Did you just get in? </TD></TR>
</TABLE>
</TD></TR></TABLE>
<BR>
<CENTER><FONT STYLE="font-size:12pt" COLOR=#572119><B>
Top 5<BR>Words
</CENTER></B></FONT>
<TABLE WIDTH=100% CELLPADDING=0 CELLSPACING=0 BORDER=1
BGCOLOR=#DFCCAC BORDERCOLOR=#572119>
<TR><TD>
1. Foo<BR>
2. Bar<BR>
3. Baz<BR>
4. Frob<BR>
5. Grok
</TD></TR></TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
<%
rs.close
set rs=nothing
conn.close
set conn=nothing
%>
Our bootstrap is finished! I've uploaded a variation of Listing 9 to the hacker phrases Web site. Note that every time you load the home page or click the Hacker Sayings link, a new hacker phrase pops up. Next week we'll look at implementing the autonomous content link (see the dashed line in Figure 6). If you want to preview the material we'll cover in the next and future articles, go ahead and check out http://www.professorf.com. Meanwhile, your homework assignment is to implement a bootstrap for your personal Web business.
){kind=link}
Figure 6 Autonomous content link (dashed line).