Using ASP to build website forum DIY-1

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.