Use ASP to create a website forum DIY (i)

Foreword: Now on the internet forum everywhere has, but I do not know whether you have encountered this kind of situation: in the messy discussion information to look for oneself wants the content, is a very time-consuming laborious matter, until later, you do not want to see what forum. So can we add the audit function to the content of our website forum? That is, all users sent to the forum article, not immediately be displayed, must go through the webmaster or moderator after review, think that there is value, it is necessary to recommend to other netizens, published. Otherwise it will be deleted, lest waste space, make a bit like the essence of the meaning of the region, so that your forum than the General Forum, can provide to the content of clear. Put on your website, should be able to attract more netizens to visit. This idea can certainly be achieved, and I will briefly explain how to make it.

Note: This article is intended for readers who have a certain understanding of Access databases, HTML, and ASP.

I. Structure analysis of the forum

Through the above functional requirements analysis, we can divide the production of the Forum into four parts:

(1) User registration and Management module: Because the introduction of webmaster or moderators, in the forum must be able to authenticate it. The function of this module is to manage the registered netizens and to provide relevant inquiries. For example, the query specifies the author's all published articles, query the most current published article 10 users, and so on. If your forum is not very large, this module can be omitted as only with moderator authentication function, and remove those about the registration, query parts.

(2) Article Display module: Show all moderators audited, feel worthy of recommendation of the article.

(3) Published article module: for registered users to provide insights to the place, published after the moderator for review.

(4) Article review module: The moderator of all nets published but not reviewed articles to deal with, has decided to publish or delete.

After understanding the specific functional requirements, you can press the module to start the design of the forum. Of course, these modules are only functional to the Forum structure division, in fact, can not completely separate them out of the design. For this small application, there is no need for a full modular design, in good planning, directly write the program code may be simpler.

There are two general approaches to the implementation of the Forum: file or database. In contrast, using a database to be simple and efficient, to better control the forum, but also to provide some validation and protection of data. Here I am using an Access database, and access should be competent for a small and medium sized application in general.

From the above analysis to know that there should be four tables, below I give the structure of each table.

(1) Author table (for storing author's message):

ID: Text type, required. is the user code.
Password: text type, required.
Nickname: Text type, required.
EMAIL: Text type, required.
Title: Number type, required. -1 on behalf of ordinary users, 0 for the webmaster. The number greater than 0 represents the moderator.
Article number: Number type, required. The total number of articles published by Netizens.
Name: text type, optional.
Sex: Text type, optional.
Tel: text type, optional.

(2) Content table (used to store specific article content and related information):

ID: Automatically numbered and indexed to speed up the search.
Kanban ID: A number type from a Kanban list that represents the Kanban that the article belongs to.
Subject ID: A numeric type from a topic table that represents the topic to which the article belongs.
Author ID: Text type, from the author table, representing the author of the article.
Date: Date/Time type, preset initial value is function now (), automatically takes the system current time for its values.
Title: Text type. The title of the article.
Publish: Yes/no type, for "true" when the article has been reviewed and can be published; "No" to indicate that the article is yet to be reviewed.
Recommendation: Number type, the recommended degree of the article.
Content: Memo type, article details.
Number of clicks: Number type, the number of clicks on the article.

(3) Kanban list (used to store information about Kanban):

ID: Automatically numbered, and the index is also set for it.
Name: text type, name of Kanban.
Board main: Text type, Kanban moderator ID.
Subject number: Number type, number of topics included in Kanban.

(4) Theme tables (for information on the subject):

ID: Automatically number and index it.
Title: Text type, representing the topic name.
Kanban: A number type from a Kanban list that represents the Kanban to which the subject belongs.
Article count: Number type, the number of articles included in the topic.

All the tables are finished, but the database design is not finished, so we also need to establish a relationship between the tables, so that the database can perform some dependency checks to avoid data errors. Another benefit of establishing a relationship between tables is that it makes it easy to create complex join queries.

Typically, when we manipulate a database in ASP, it is a query that is generated when the execution is used, and then executed by passing it to the database. And here we're going to use a storage query. The stored procedure has more advantages than the execution time query.

It is stored in the database, independent of the ASP program code, making it easier to build and modify, and query more efficient, faster, you can debug the later put on the ASP page to use, can avoid a lot of problems. and the ASP program code that uses the storage query is easier to read and modify. It may be annoying to use SQL queries in ASP, especially if those commas, semicolons, or whatever, will make mistakes. When you use a storage query, you don't have to worry about these issues. Of course, there are some places to pay attention to the use of stored procedures, in the following I will explain the use of its methods. It's easy to create a stored procedure in Access, and I'm not going to talk about it any more. Here I only give the SQL statement program code for each query.

I keep all the queries that I want to use as stored in the database, mainly the following:

(1) By ID Inquiry article:

SELECT topic table. Title as topic name, Kanban list. Name as Kanban name, Content table. *
From topic table INNER Join (content table INNER join Kanban list on content table. Kanban ID = Kanban list. id) on topic table. ID = content table. Subject ID
WHERE ((Content table. ID) =[articleid]);

(2) Moderator password query:

SELECT kanban list. Board Master, author table. Password
From author table INNER JOIN kanban list on author table. ID = kanban list. Board Master
WHERE ((kanban list. ID) =[id];

(3) Query Author:

SELECT Author table. *
From author table
WHERE ((author table. ID) =[id]);

(4) Publish a list of articles:

SELECT [Content table]. [ID], [content table]. [title], [content table]. [Author ID] As author, [Content table]. [Date], [content table]. [Recommended degree], [content table]. [Number of clicks] As number of clicks.
From content table
WHERE (([[Content Table].[ Subject ID]) =[topicindex] and ([Content table].[ )) (=true));

List of articles not published:

SELECT content table. ID as article ID, subject table. ID as topic ID, subject table. Title as topic, Content table. Title as title, Content table. Author ID as author, content table. Date as Date
From topic table INNER JOIN Content table on topic table. ID = content table. Subject ID
WHERE ((Content table. Published) =false) and ((Content table. Kanban ID) =[boardid]);

(5) List of topics:

SELECT topic table. *, Kanban list. Name as Kanban name
From Kanban list INNER JOIN topic table on Kanban list. ID = Topic table. kanban
WHERE ((Subject table. Kanban) =[boardindex]);

There are also some inquiries, because most of them are similar, I will not list them.

In the above query, you can see some things surrounded by "[" and "]", that is, query parameters. A parameter value needs to be given at execution time, and then the parameter value is brought into the query statement before it can be executed. It is also important to note that when creating those inner join queries, it is necessary to add the relationship between the tables to the Design view, otherwise it is not possible to automatically generate INNER JOIN query statements.

Here, the database design is over, the future work is the ASP.

Second, the construction

1. Building the main form

First to give a form to allow users to enter the registration message, these are HTML content, and put aside, we take a closer look at the specific implementation of the registered ASP script.

(1) Change the single quotation mark in the data to two single quotes and enclose the single quotation mark before and after

Function sqlstr (data)
Sqlstr = "'" & Replace (data, "'", "" ") &" "
End Function

Note: This is a custom function that converts single quotes (') in user input to two single quotes ('). In ASP, the string is surrounded by double quotes, so the above "'" means a string with only one single quote. The reason to change one single quotation mark into two single quotes is that it is used to represent variables in a SQL statement enclosed in single quotes. To avoid confusion, the single quotation marks in the string are represented by two single quotes. All user input is embedded as a variable in the SQL statement, so this function is essential.

(2) Storage preparation

Id=request ("id")
Password=request ("password")
Nickname=request ("nickname")
Email=request ("email")
Sex=request ("Sex")

Note: It is not necessary to save the content from the user input form in a variable, but it is easier to read and write.

If Request ("name") = "" Then Name= "Else name=request (" name ")
If Request ("phone") = "" Then phone= "" Else phone=request ("phone")

Because these content is not mandatory, in order to prevent users from not entering any content, and caused errors in database operations, you must not fill in the field with a space to replace.

(3) Establish a connection

Set conn = Server.CreateObject ("ADODB. Connection ")
Conn. Open "Driver={microsoft accessdriver (*.mdb)};d bq=" & Server.MapPath ("Bbssystem.mdb")

Note: This section is to establish a database connection, the name of the database is Bbssystem.mdb, the only thing to note in this paragraph is the application of the Server.MapPath function. In general, where the specific directory is involved, do not use the directory name directly, instead of using the Server.MapPath function instead. Make good use of Server.MapPath and Request.ServerVariables () and other functions, you can have a better Web application portability.

Set cmd = Server.CreateObject ("Adodb.command")

(4) query whether the author already exists

Set cmd. ActiveConnection = conn
Cmd.commandtext = "query Author"
ReDim param (0) ' declares an array of arguments
Param (0) = CStr (ID) ' Cint not to be ignored
Set rs = cmd. Execute (, param)

Note: This section is used to execute the stored procedure. There are many ways to execute queries in ADO, but you can only use command objects for stored procedures. First, a command object called CMD is established, then the Conn Connection object is set to the ActiveConnection attribute of the Cmd object, the query name "query Author" is set to the CommandText attribute, and then the query parameter is assigned a value. We declare a parameter array param (0), because there is only one argument in the query author query, so the array has only one component. In general, there are several parameters in the query, it is necessary to declare a corresponding number of components of the parameter array. and the order in which the parameters appear is corresponding to the order of the components in the array. In the process of using a parameter query, it is particularly noteworthy that the type of the parameter should be strictly matched, or this will be an error, so the above CStr () type conversion function is indispensable.

If not (rs.eof or RS.BOF) then
Response.Write "Error, you entered the ID number has been occupied, please change another try again!" "
Else
sql = "Insert into author table (ID, nickname, Email, password, name, school, department, Sex, telephone) Values ("
sql = SQL & Sqlstr (ID) & ","
sql = SQL & Sqlstr (nickname) & ","
sql = SQL & sqlstr (email) & ","
sql = SQL & sqlstr (password) & ","
sql = SQL & SQLSTR (name) & ",&", "
sql = SQL & sqlstr (Sex) & ","
sql = SQL & SQLSTR (phone) & ")"
Conn. Execute SQL

Inserts the data into the database using a SQL INSERT statement. In fact, this query can be made into a stored procedure in the database, I stole a bit lazy:-) but the contrast can also see the benefits of stored procedures, the implementation of the query write it is too troublesome.