An Article Rating System With ASP - Creating the database

(Page 2 of 5 )

For the sake of this article, let's pretend that we run a web site that sells music CD's. We want each visitor to be able to rate CD's on a scale of 1 to 10, with 1 being the worst and 10 being the best.

For our Microsoft Access database we will be using two tables: one to hold the details of each CD, and another to hold the ratings for each CD, as shown below:





Each of the tables only contains four fields, which are described below:

The cds table:

• cdId: An autonumber field that gives each CD its own numerical identifier.
• title: The name of each CD, such as "100% Greatest Hits".
• artist: The name of the artist who composed the CD.
• summary: A blurb about the CD and its artist(s).
• price: The cost of the CD in dollars.

The ratings table:

• ratingId: An autonumber field that gives each rating its own numerical identifier.
• rating: The actual rating that the user gave to the CD, such as 1, 4, 10, etc.
• ip: The IP address of the user that rated the CD. We will use a combination of cookies and IP address checking to make sure that each user can rate a particular CD only once.
• cdId: The autonumber of the CD for which this rating has been cast.

Adding, editing and deleting CD's to/from the database is beyond the scope of this article, so let's assume that we have the following 3 CD's in the cds table already:



With the database created, we're ready to create the ASP code to connect to it and display the CD's with the ability to be rated. Let's do that now.

Displaying the CD's
For this article we’re going to list all of the CD's on one page, along with a form that allows each one to be rate individually. First off, we need to connect to the database. I have chosen to use a DSN (data source name) to manage the database connection details, so here are the steps to create it:

1. Click start -> programs -> administrative tools -> data sources (ODBC)
2. Click on the system DSN tab then the add button
3. Select Microsoft Access driver and click finish
4. Enter the name of the DSN as "cds"
5. Click on the select button and browse to your Access database
6. Click OK and you're done

Next, we want to use this DSN to connect to our Access database. We will create a new file called showcds.asp and enter the following code into it:

<%

dim conn
dim rs

set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")

conn.Open "DSN=cds"

rs.ActiveConnection = conn
rs.Open "SELECT * FROM cds ORDER BY title ASC"

As shown above, we create one ADO connection and one ADO recordset and connect to our Microsoft Access database through our DSN with the following line:

conn.Open "DSN=cds"

Once connected, we set the ActiveConnection property of the recordset to conn and query the database to return all of the CD's ordered by their title with the following line:

rs.Open "SELECT * FROM cds ORDER BY title ASC"

We now have a recordset containing all of the CDs in our cds table. We setup a loop with while…wend to loop through each CD, displaying its details as well as a HTML form that shows radio buttons to allow visitors to rate each CD:

while not rs.EOF
%>
<font face="Verdana" size="2" color="black">
<h2><%=rs.Fields(1).Value%></h2>
<b>Author:</b> <%=rs.Fields(2).Value%><br>
<b>Summary:</b> <%=rs.Fields(3).Value%><br>
<b>Price:</b> <%=FormatCurrency(rs.Fields(4).Value, 2)%><br>
</font>
<br>
<form name="frmRate<%=rs.Fields(0).value%>" action="rate.asp">
<input type="hidden" name="cdId" value="<%=rs.Fields(0).value%>">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="black">
<font face="Verdana" size="1" color="white">
<%
for i = 1 to 10
Response.Write "&nbsp;" & i & " <input name='rating' type='radio' value='1'> "
next
%>
</font>
<input type="submit" value="Rate It!">
</td>
</tr>
</table>
</form>
<hr size="1" color="#08496B" NOSHADE>
<%
rs.MoveNext
wend
%>

The loop above stars by outputting the title, author, summary and price for each CD:

<h2><%=rs.Fields(1).Value%></h2>
<b>Author:</b> <%=rs.Fields(2).Value%><br>
<b>Summary:</b> <%=rs.Fields(3).Value%><br>
<b>Price:</b> <%=FormatCurrency(rs.Fields(4).Value, 2)%><br>

I used the FormatCurrency function to display the price in the format of $xx.xx, instead of the normal xx.xx, which is what the value of the price field returns by default.

To display a rating form for each CD, we create an HTML form with a loop to output radio buttons that will let our visitors rate each CD from 1 to 10, as shown below:

<form name="frmRate<%=rs.Fields(0).value%>" action="rate.asp" method="post">
<input type="hidden" name="cdId" value="<%=rs.Fields(0).value%>">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="black">
<font face="Verdana" size="1" color="white">
<%
for i = 1 to 10
Response.Write "&nbsp;" & i & " <input name='rating' type='radio' value='1'> "
next
%>
</font>
<input type="submit" value="Rate It!">
</td>
</tr>
</table>
</form>

A new form is output for each CD, and all ratings are sent to rate.asp as HTML form-posted variables. Once the while loop completes, we have a list of CD's from the database shown in our browser with a rating form for each, like this:



At this point we haven't added the ASP code to showcds.asp to display the average rating for each CD. We will now take a look at rate.asp, which adds the rating to the database and then we will come back to look at the code to display the average rating for each CD.

Next: Rate.asp >>
 

More ASP Articles
More By Annette Tennison

Comments On: An Article Rating System With ASP

>>> Be the FIRST to comment on this article!