We begin with a simple html file that provides a form and a text box for entering a search term:
<form method="POST" action="list_found_players.asp"
name="club_search">
<p><input type="text" name="clubname"
size="20">
<input type="submit" value="Submit"
name="B1">
<input type="reset" value="Reset"
name="B2"></p>
</form>
The action for the form is to run an asp file, which will process the request using the search term as its parameter:
<%
sname=request.form("clubname")
openstr = "driver={Microsoft Access Driver (*.mdb)};" & "dbq="
& Server.MapPath("soccer2.mdb")
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open openstr
sql = "SELECT * FROM players WHERE ((players.[Club Name])= ' "
&sname& " ' ); "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn, 0, 1, 1
%>
Here we request the search term from the form in the html file and add it to a SQL query. We have to use a new variable name, 'sname', and the syntax ' " &sname& " ' - if we don't get this exactly right it doesn't work.
This code will search the specified table and return any rows where the search term matches the key field contents.
We can apply this to any database we choose, for example one that collects 'web logs' from people who make contributions from a web page.
We can put what we have learned so far into practice in an example, building a web log ('blog') service. This will consist of a home page and five additional pages for submitting blogs, reading past blogs and searching for blogs by subject. A blog (web log) is run by an individual who wants to comment on things. It makes sense to use a database for this rather than linked pages so that blogs can be categorised by topic and past blogs can be found by searching the database.
Blogging began around 1998 (see History of Blogging) and has, since then, expanded into a global media phenomenon of great power and versatility. Blogging gives people without other direct access to mass media the opportunity to express their opinion and to reveal information that might not be disclosed by the normal channels. It has become a particularly powerful tool in situations of civil unrest, notably in Iran, and was also responsible in part for the demise of the editor of the New York Times, Howell Raines, victim of a blogging campaign after a scandal over reporter Jayson Blair (see Media Reality check). For a sample site see blogger.com or search Google for 'blog'.
To set up a blog site make sure you have access to a web server with exclusive access to your own pages (this example uses ASP and the Microsoft Internet Information Server). Next set up a database something like this:
This stores the basic data for a simple blog, a number for each entry (the primary key), the entry itself, the date and time and the category or subject of the blog. Save this database in the same directory as your html and asp files.
The home page is a simple html page with a list of the other pages in the system. The other pages will be:
The blog creation page consists of a form and some code to save the entries in the database:
<form method="POST" action="<%=request.servervariables("blogger.asp")%>">
Message:<textarea cols="40" rows="6" name="blog">
</textarea><br>
Category: <input name="category" type="text"><br>
<input name="submit" type="submit">
</form>
This form allows a user to enter a blog, along with their name and the subject. Note that the <textarea> tag corresponds to the memo field in the Access database. The name of the sender is tied to the input box of that name, as is the category type. This code trims the user's input and replaces some of the characters with html or appropriate characters, for example the carriage return and '<' and '>'.
<%
category=replace(trim(request.form("category")),"'","''")
blog=replace(trim(request.form("blog")),"'","''")
blog=replace(blog,chr(13)&chr(10),"<br>")
blog=replace(blog,chr(13),"<br>")
blog=replace(blog,chr(10),"<br>")
blog=replace(blog,"<","<")
blog=replace(blog,">",">")
%>
The next block of code opens an ADO table, defines a SQL statement and opens the database table with the query:
<%
dblog=request.form("blog") 'get blog
contents from form
dbcategory=request.form("category") 'get
blog type from form
openstr = "driver={Microsoft Access Driver (*.mdb)};" & "dbq="
& Server.MapPath("blog.mdb")
Set connBlog= Server.CreateObject("ADODB.Connection")
connBlog.Open openstr
sql = "SELECT * FROM tblBlogs ; "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "tblBlogs ", connBlog, 3, 2, adCmdTable
The final section of code in blogger.asp adds a new record to the data table, updates it and then closes the ADO connection:
rs.AddNew
rs("chrBlogEntry")=dblog
rs("dtDateTime")=Now()
rs("idCategory")=dbcategory
rs.Update
rs.Close
Set rs = Nothing
connBlog.Close
Set connBlog = Nothing
%>
This allows the user to enter a search term and to find all postings in that category. The page provides a form to allow the user to enter the search term and submit it to the database:
<form method="POST" action="<%=request.servervariables("search_by_poster.asp")%>">
Sender:<input name="category" type="text"><br>
<input name="submit" type="submit">
</form>
The following code opens the ADO connection and defines and executes a SQL query:
<%
scategory=request.form("category")
openstr = "driver={Microsoft Access Driver (*.mdb)};" & "dbq="
& Server.MapPath("blog.mdb")
Set connBlog= Server.CreateObject("ADODB.Connection")
connBlog.Open openstr
strsql="select * from tblBlogs where ((tblBlogs.idCategory)= '"&scategory&"'
); "
set rs=connBlog.execute(strsql)
Note how a local variable is set up to take the value from the form and how this is used in the SQL query. The next block of code loops through the records in the dataset created by the SQL query:
response.write"<table border=1>"
do while not rs.eof
response.write "<tr><td bgcolor=#CCFFCC>Subject:<b>"
& rs.fields("idCategory") & "</b></td>"
response.write "<td bgcolor=#FFCCFF>Time:" &
rs.fields("dtDateTime") & "</td></tr>"
response.write "<td bgcolor=#FFFFCC>Entry:" &
rs.fields("chrBlogEntry") & "</td></tr>"
rs.movenext
loop
Note how the html is output by the asp code to produce the web page. As before the connection is closed:
response.write "</table>"
set rs=nothing
connBlog.close
set connBlog=nothing
%>
The TopTen and All_Blogs pages are similar in the way they use a SQL query to find the data:
strsql="select * from tblBlogs order by dtDateTime desc"
strsql="select top 10 * from tblBlogs order by dtDateTime desc"
They then use the same code as Search_by_Category to display the records they have found. And that's just about it, put these together and you have a working blog system. To create a message board system you need to make the database accessible to all users who are able to submit messages and you also need to add a field for the ID of the poster.