Hooray! - Sorting and Searching Database Tables, Part 3

Hooray! - Creating a Customized homepage (using a database), Part 3
By Christopher Miller

This article is the third part in a 4 part series (read Part 1 | Part 2) . The series will be a complete tutorial of creating your own search engine and web portal called - Hooray! I will be discussing the following techniques:

- continued -

Sorting and Searching Database Tables
Adding, Deleting, and Updating Records in a Database
Creating a Customized homepage (using a database to store preferences)
Putting It All Together (combines all previous articles as well as others to create Hooray!)

Some of you may have read my article on creating a customized homepage. The article discusses how to create a homepage using cookies to store the user preferences. This article is along the same lines (and even uses the same look and feel) but with one exception, we will be using a database to store the user preferences. The only cookie we need is for storage of the unique user ID that we will be using to display the custom page. This will allow us to eliminate the need for a login screen.

This method is much easier to manage and will produce a much better end result. So let's get started...

Here is an example.

We will be using 7 ASP pages in all:

global.asa
default.asp
register.asp
my_hooray.asp
edit_globals.asp
edit_links.asp
edit_emails.asp

The database (hooray.mdb) will contain 4 tables with the format below:

tblUsers:

uID AutoNumber

uName Text

uEmail Text

tblFormatting:

fID AutoNumber

fTextColor Text

FLinkColor Text

fVLinkColor Text

fALinkColor Text

fBGColor Text

fUserID Text

fTableColor Text

tblLinks:

lID AutoNumber

lUserID Number

lLInk Text

lName Text

tblAddressBook:

aID AutoNumber

aUserID Number

aEmail Text

aName Text

Let's start with the global.asa file. I am simply using this to store the path of the database. This allows us to move the database to a different location without much modification.

SUB Session_OnStart

Session("DatabasePath") = "C:\databases\"

END SUB

</SCRIPT>

Next, the default.asp page simply checks for the existance of a cookie on the client�s machine. If there is no cookie stored, we send the user to a registration page (register.asp).

<%
'-- check for User Id

If Request.Cookies("UserID") = "" Then
Response.Redirect("register.asp")
Else
Session("UserID") = Request.Cookies("UserID")
Response.Redirect("my_hooray.asp")
End If
%> Which brings us to the register.asp page. We will be using this page to create the user profile in the database. I am only going to be using 2 fields to create a user: name and email address. You can, of course, grab any data you wish from the users. The HTML is irrelevant, so I am going to concentrate on the ASP. We first need to add the user to the database when the 'register' button is clicked:

<%
If Request.Form("btnRegister") = "Register" Then

Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName

varDatabaseName = "hooray.mdb"
SQL = "SELECT * FROM tblUsers;"
%>

<%
RecordSet.AddNew

RecordSet.Fields("uName") = Request.Form("txtName")
RecordSet.Fields("uEmail") = Request.Form("txtEmail")

RecordSet.Update
RecordSet.ReQuery

'-- get New User ID

If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.EOF

If RecordSet.Fields("uName") = Request.Form("txtName") Then
If RecordSet.Fields("uEmail") = Request.Form("txtEmail") Then

Session("UserID") = RecordSet.Fields("uID")

End If
End If

RecordSet.MoveNext
Loop
%>

You will notice that I am using includes for the database opening and closing codes. I got into this habit because it is much easier to manipulate the code that is used over and over. I then store the database name and SQL into strings located on each individual page so that I can customize the database connection as I wish.

Once we have the new user in the database, and we have their unique ID stored in a session variable, we can add them to the formatting database. I have set the default values right in the database rather than on the homepage itself to make the code a lot cleaner and easier to manage.

<%
'-- Add new user to the formatting database

SQL = "SELECT tblFormatting.fUserID FROM tblFormatting;"
%>

<%
RecordSet.AddNew

RecordSet.Fields("fUserID") = Session("UserID")

RecordSet.Update
%>

Once the user is aded into the formatting table, we need to set the cookies:

<%
'-- Set the user id cookie

Response.Cookies("UserID") = Session("UserID")
Response.Cookies("UserID").Expires = Date() + 365

'-- Send the user to their homepage when done

Response.Redirect("default.asp")

End If
%>

That is it for register.asp, let's move on to my_hooray.asp. This is the page that the user will be using as their personal homepage. The first thing is to grab the user's preferences from the database:

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName
Dim varUserName, varUserEmail
Dim varfTextColor, varfLinkColor, varfvLinkColor
Dim varfALinkColor, varfBGColor, varfTableColor

varDatabaseName = "hooray.mdb"

'-- Default SQL to get name and formatting

SQL = "SELECT tblUsers.uName, tblFormatting.*, tblUsers.uID, tblUsers.uEmail "
SQL = SQL & "FROM tblFormatting INNER JOIN tblUsers ON tblFormatting.fUserID = tblUsers.uID "
SQL = SQL & "WHERE (((tblUsers.uID)=" & Session("UserID") & "));"
%>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst

varUserEmail = RecordSet.Fields("uEmail")
varUserName = RecordSet.Fields("uName")
varfTextColor = RecordSet.Fields("fTextColor")
varfLinkColor = RecordSet.Fields("fLinkColor")
varfVLinkColor = RecordSet.Fields("fvLinkColor")
varfALinkColor = RecordSet.Fields("fALinkColor")
varfBGColor = RecordSet.Fields("fBGColor")
varfTableColor = RecordSet.Fields("fTableColor")

End If
%>

I am storing the 'general' preferences in style sheets rather than in a <body> tag:

<STYLE TYPE="text/css">
<--
BODY {
color : <%= varfTextColor %>;
background-color : <%= varfBGColor %>;
}
A:Active {
color : <%= varfALinkColor %>;
}
A:Visited {
color : <%= varfVLinkColor %>;
}
A:Link {
color : <%= varfLinkColor %>;
}
-->
</STYLE>

After we have our homepage set-up the way we want (again, I am not going to concentrate on the HTML, you can set up the page however you want), we can start inserting the links and the address book:

<%
'--Insert links

SQL = "SELECT tblLinks.lLink, tblLinks.lName "
SQL = SQL & "FROM tblLinks "
SQL = SQL & "WHERE (((tblLinks.lUserID)=" & Session("UserID") & ")) "
SQL = SQL & "ORDER BY tblLinks.lName;"
%>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
Do Until RecordSet.EOF
%>

<%
RecordSet.MoveNext
Loop
End If

If RecordSet.recordCount = 0 Then
Response.Write("<i>You have no links saved...</i>")
End If
%>

<%
'--Insert Email addresses

SQL = "SELECT tblAddressBook.aEmail, tblAddressBook.aName "
SQL = SQL & "FROM tblAddressBook "
SQL = SQL & "WHERE (((tblAddressBook.aUserID)=" & Session("UserID") & ")) "
SQL = SQL & "ORDER BY tblAddressBook.aName;"
%>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
Do Until RecordSet.EOF
%>

<%
RecordSet.MoveNext
Loop
End If
%>

You will notice the <option> tag that I insert my email address into. That is because I have set-up the page to have an email form right on it (as with the previous customized homepage article). I prefer to use CDONTS to send my mail, but there are dozens of ASP mail components that you can use. I will let you decide for yourself.

Let's move onto edit_globals.asp, edit_emails.asp, and edit_links.asp. All three pages do essentially the same thing. I use a SQL statement to grab only the records that belong to a particular user, and then display and save new values. I am simply going to list all 3 pages below (including the HTML).

edit_globals.asp

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName
Dim varUserName, varUserEmail
Dim varfTextColor, varfLinkColor, varfvLinkColor
Dim varfALinkColor, varfBGColor, varfTableColor
varDatabaseName = "hooray.mdb"

'-- Default SQL to get name and formatting

<%
'-- Insert New values

If Request.Form("btnSave") = "Save" Then

If Not RecordSet.BOF Then
RecordSet.MoveFirst

RecordSet.Fields("uEmail") = Request.Form("txtEmail")
RecordSet.Fields("uName") = Request.Form("txtName")
RecordSet.Fields("fTextColor") = Request.Form("txtTextColor")
RecordSet.Fields("fLinkColor") = Request.Form("txtLColor")
RecordSet.Fields("fVLinkColor") = Request.Form("txtVColor")
RecordSet.Fields("fALinkColor") = Request.Form("txtAColor")
RecordSet.Fields("fBGColor") = Request.Form("txtBGColor")
RecordSet.Fields("fTableColor") = Request.Form("txtTableColor")

RecordSet.Update
RecordSet.ReQuery

End If
End If

'-- Get values

If Not RecordSet.BOF Then
RecordSet.MoveFirst

End If
%>

<font size="4">Editing Globals for <%= varUserName %></font><P>

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="right">Name:</td>
<td><input type="text" name="txtName" value="<%= varUserName %>" size="20"></td>
</tr>
<tr>
<td align="right">Email:</td>
<td><input type="text" name="txtEmail" value="<%= varUserEmail %>" size="20"></td>
</tr>
<tr>
<td align="right">Text Color:</td>
<td><input type="text" name="txtTextColor" value="<%= varfTextColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Background Color:</td>
<td><input type="text" name="txtBGColor" value="<%= varfBGColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Table Color:</td>
<td><input type="text" name="txtTableColor" value="<%= varfTableColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Link Color:</td>
<td><input type="text" name="txtLColor" value="<%= varfLinkColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Visited Link Color:</td>
<td><input type="text" name="txtVColor" value="<%= varfVLinkColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Active Link Color:</td>
<td><input type="text" name="txtAColor" value="<%= varfALinkColor %>" size="20"></td>
</tr>
<tr>
<td colspan="2" align="center"> </td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" name="btnSave" value="Save"></td>
</tr>
</table>

</form>

<p>

</center>

</body>
</html>

edit_emails.asp

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName

varDatabaseName = "hooray.mdb"

SQL = "SELECT tblAddressBook.*, tblAddressBook.aUserID "
SQL = SQL & "FROM tblAddressBook "
SQL = SQL & "WHERE (((tblAddressBook.aUserID)=" & Session("UserID") & "));"
%>

<%
If Request.QueryString("Action") = "Delete" Then

Dim RecordToDelete

RecordToDelete = CInt(Request.QueryString("ID"))

If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.Fields("aID") = RecordToDelete

RecordSet.MoveNext
Loop

RecordSet.Delete
RecordSet.Requery

End If

If Request.Form("btnSave") = "Save Email" Then

RecordSet.AddNew

RecordSet.Fields("aUserID") = Session("UserID")
RecordSet.Fields("aName") = Request.Form("txtName")
RecordSet.Fields("aEmail") = Request.Form("txtEmail")

RecordSet.Update
RecordSet.Requery

End If
%>

<table border="1" cellpadding="3" cellspacing="0" width="100%">
<tr>
<td align="center"><b>Action</b></td>
<td align="center"><b>Name</b></td>
<td align="center"><b>Eamil</b></td>
</tr>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.EOF
%>

<tr>
<td><a href="edit_emails.asp?Action=Delete&ID=<%= RecordSet.Fields("aID") %>">Delete</a></td>
<td><%= RecordSet.Fields("aName") %></td>
<td><%= RecordSet.Fields("aEmail") %></td>
</tr>

<%
RecordSet.MoveNext

Loop
%>

</table>

Name: <input type="text" name="txtName" size="30">
Email: <input type="text" name="txtEmail" size="30">
<input type="submit" name="btnSave" value="Save Email">

</form>

</body>
</html>

edit_links.asp

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName

varDatabaseName = "hooray.mdb"

SQL = "SELECT tblLinks.*, tblLinks.lUserID "
SQL = SQL & "FROM tblLinks "
SQL = SQL & "WHERE (((tblLinks.lUserID)=" & Session("UserID") & "));"
%>

<%
If Request.QueryString("Action") = "Delete" Then

Dim RecordToDelete

RecordToDelete = Int(Request.QueryString("ID"))

If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.Fields("lID") = RecordToDelete

RecordSet.MoveNext
Loop

RecordSet.Delete

End If

If Request.Form("btnSave") = "Save Link" Then

RecordSet.AddNew

RecordSet.Fields("lUserID") = Session("UserID")
RecordSet.Fields("lName") = Request.Form("txtName")
RecordSet.Fields("lLink") = Request.Form("txtURL")

RecordSet.Update
RecordSet.Requery

End If
%>

<table border="1" cellpadding="3" cellspacing="0" width="100%">
<tr>
<td align="center"><b>Action</b></td>
<td align="center"><b>Name</b></td>
<td align="center"><b>URL</b></td>
</tr>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.EOF
%>

<tr>
<td><a href="edit_links.asp?Action=Delete&ID=<%= RecordSet.Fields("lID") %>">Delete</a></td>
<td><%= RecordSet.Fields("lName") %></td>
<td><%= RecordSet.Fields("lLink") %></td>
</tr>

<%
RecordSet.MoveNext

Loop
%>

</table>

Name: <input type="text" name="txtName" size="30">
URL: <input type="text" name="txtURL" size="30">
<input type="submit" name="btnSave" value="Save Link">

</form>

</body>
</html>

That is pretty much all there is to it. I have left the code pretty wide open so you can customize it however you want. You may want to incorporate (and you will see it on the example) a 'start over' option. I added this so I could test and debug the code and it seemed to me like it may be a good feature to ad anyway. Here is the 'start over' code:

<%
Dim Item
Dim DataConnection, cmdDC, RecordSet, SQL

For Each Item In Request.Cookies
Response.Cookies(Item) = ""
Next

varDatabaseName = "hooray.mdb"

SQL = "SELECT tblUsers.uID, tblUsers.* "
SQL = SQL & "FROM tblUsers "
SQL = SQL & "WHERE (((tblUsers.uID)=" & Session("UserID") & "));"
%>

<% RecordSet.Delete %>

<%
Session.Abandon

Response.Redirect("default.asp")
%>

Keep your eyes open for my next arrticle that will put this (and all of my other articles) into one giant application called Hooray!. Enjoy!

By Christopher Miller...

Related Articles

Hooray!, Part 1

Hooray!, Part 2

Attachments:

Download the code in ZIP format