Everyone must be familiar with online surveys. A question and several answers are given for users to fill in, and then the results are saved to the database, statistics are automatically performed, and finally a statistical graph is given. In this issue, follow me and make an online survey system.
1. Functional design:
Does such a simple system need to be functionally designed? Some people may find it strange, but having said that, no matter what kind of system it is, if you do functional design first, you can always have a clearer understanding of the system. Let’s take a look at what online surveys can do. The basic function has been mentioned above, which is to give a question and several answers, then make statistics, and finally give a graph. On this basis, we can consider adding a time period (validity period) to a survey. During this time period, the survey is valid. After this period, the survey will automatically end. Additionally, we can specify that a user can only submit answers once at a time. If you want to be more restrictive, you can specify that an IP can only submit an answer once. However, this may only allow one person in an Internet cafe to submit. For the questions in the survey, some may be single-choice questions and some may be multiple-choice questions. Finally, let’s talk about the statistical graph. In the statistical graph, the answers, the number of voters for each answer, and the proportion of votes for each answer should be shown. Generally, it is enough to use a horizontal diagram, which is easier to implement. Of course, if you want to change it to a vertical diagram, you can also do it.
Now based on the above, the functions of the online survey are summarized as follows:
1. The data is saved in the ACCESS 2000 database;
2. Each user can vote once per visit
3. The statistics of each survey are given and displayed with statistical charts
4. Each Each survey has an expiration date and ends automatically after expiration. Only the results of completed surveys can be viewed.
5. Administrators can add surveys and modify survey answers (add, modify, delete, modify type).
6. For surveys that have ended, administrators can only delete the survey but cannot modify the answers.
7. There is only one administrator (single user)
2. Database design
Now let’s design the database. According to the functional requirements, there must be at least three tables, one is the administrator table, the second is the survey table, and the third is the survey results table. The database file name is survey.mdb and can be changed to .asp. If so, please make corresponding modifications in the ASP program.
Table 1. Administrator table table name: manage
-------------------------------------------------- ---------------
Field type length description
-------------------------------------------------- ---------------
manage_id automatic numbering - not used here, will be expanded in the future
manage_username Text 15 Administrator username
manage_password Text 15 Administrator password
-------------------------------------------------- ---------------
After creating the manage table, add a new record and fill in your administrator username and password. Here you fill in xmxoxo
table 2. Survey table name: survey
-------------------------------------------------- ---------------
Field type length description
-------------------------------------------------- ---------------
survey_id automatic numbering - incremental, primary key, indexed, no duplication
survey_question text 255 survey question
survey_type whether - type, no: single choice yes: multiple choice
survey_stime date - long date, start time
survey_etime date - long date, end time
-------------------------------------------------- ---------------
Table 3. Survey end form Table name: survey_vote
------------------------------- ----------------------------------------
Field type length description
-------------------------------------------------- ---------------
vote_no automatic numbering - increasing, primary key, indexed and no duplication
vote_id long integer - indexed and repeated, decimal place 0
vote_answer Text 100 Survey Answers
vote_count long - number of votes
-------------------------------------------------- ---------------
Among them, the survey_vote table and the id field of the survey table have a many-to-one relationship. It is not necessary to establish this relationship, but establishing a relationship will make the ideas clearer.
3.There are not many functions used here to
include files
. They are mainly used to operate the database. If you want to prevent HTML and other codes when inputting, just use server.htmlencode to process it, so there is no need for a special function.to handle. We can continue to use the included files in the previous article "Learn from me" series "Learn from me to make a tree menu".
Shared function file, file name: inc.asp
<%
''************************************************ *******************
''Common database ASP function
''************************************************ *******************
''Database constants
databasename="survey.mdb" ''Database name, if you change the name, just modify it here
''************************************************ *******************
''Open the database
sub opendb(connect)
set connect=server.CreateObject("ADODB.connection")
connect.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &_
server.MapPath(databasename)
connect.Open strconn
end sub
''************************************************ *******************
''Close database
sub closedb(connect)
connect.close
set connect=nothing
end sub
''************************************************ *******************
''Open a single table for reading
sub opentable(connect,tbname,myrs)
set myrs=server.createobject("ADODB.recordset")
rssql="select * from " & tbname
myrs.open rssql,connect,1,1
end sub
''************************************************ *******************
''Close the temporary table
sub closetable(rs)
rs.close
set rs=nothing
end sub
''************************************************ *******************
''Query database
sub searchtable(connect,sql,rs)
set rs=server.createobject("ADODB.recordset")
rs.open sql,connect,1,1
end sub
''********************************************** *********************
''Query and change the database
sub changetable(connect,sql,rs)
set rs=server.createobject("ADODB.recordset")
rs.open sql,connect,1,3
end sub
''********************************************** *********************
''Display information for debugging
Sub w(msg)
response.write msg
end sub
''********************************************** *********************
''Program interrupt for debugging
subuserstop()
response.end
end sub
%>
4. Document design
According to the traditional thinking, we always design online surveys into three parts. The first is to display the questionnaire; the second is to display the survey statistical results; the third is background management. In practical applications, we will find that the questionnaire is often displayed on a certain web page, and the results are usually displayed in a pop-up window. Backend management is directed to via another link or login form. In order to use the online survey more conveniently, we wrote the part of displaying the questionnaire as a JS script, so that the web page that needs to display the questionnaire can freely call it by referencing the script. Okay, let’s take a look at the file design
1. Inc.asp includes the file. Main function library
2, surveycode.asp displays the questionnaire program. Use a script in the main web page to call it.
3. survey.asp questionnaire list program. Lists the status of all investigations.
4. survey_vote.asp is a program that displays survey statistical results. Bring parameters to represent the questions under investigation.
5. survey_manage.asp management program.
Here, we have skipped the page design, because style design is not what we are talking about, so regarding the style, layout, CSS, etc. of the web page, please design it yourself.
5. File flow:
First write down the main flow of the program, which can make it easier to modify, expand, and transplant in the future. What is more important here is which subroutines to write in the program and how to arrange these subroutines.
1. surveycode.asp displays the survey form
<%
''Get the querystring parameter, id represents the survey number
''Determine the correctness of parameters
''Determine whether the investigation is within the validity period.
''Read survey question, type
''Output survey answers and generate survey form
''Close database and tables
%>
2. survey.asp displays all survey status
<%
''Read the database
%>
<html>
''Show all survey status and add links
</html>
3. survey_vote.asp displays statistical results.
There are two functions here, one is to display without submission, and the other is to perform statistics after submitting the answer, and then display the result. If there are no parameters, it is the first way. It can also be done in two files.
<%
''Get parameters. id represents the survey number. All data comes from the form.
''Determine whether there are parameters, if so, perform statistics first
''If not, it will be displayed directly.
''Statistical subroutine
%>
<html>
''Display subroutine
</html>
4. survey_manage.asp management program.
The management part is more complex and needs to implement more functions. Let’s first list the management functions:
1) Administrator login. Management can only be done after logging in
. 2) Log out. Exit safely when finished managing.
The management of surveys includes:
3) Add a survey. Also add survey answers
4) Modify a survey. Modify content, time, type, content of survey answers, add, delete
5) Delete a survey. An ongoing survey cannot be deleted.
Design its process for these functions
<%
''Get parameters. Action represents action, corresponding to the above functions.
''Redirect to the corresponding subroutine according to the action
''Login subroutine
''Exit the login subroutine
''Execute the subroutine of adding survey questions
''Execute the subroutine of adding survey answers
''Execute the modified survey subroutine to modify the questions and answers together
''Execute delete survey question subroutine
''Execute the delete survey answer subroutine
<html>
<%
'' Determine whether you are logged in, if not, display the login form.
''Display the corresponding form based on the action
''Show all survey subroutines
''Shows a single survey subroutine. Questions and answers are displayed together
''Show the added investigation subroutine.
''Show login form
%>
</html>
6. Code Writing
After the process design is done, writing code will be more organized. Let's start simple. Before writing the code, we need to enter some records in the database for testing. First add a survey question, several survey answers, and manually enter some statistical information.
Let's first write surveycode.asp to display the survey form. This file needs to be called in other pages, so we write it in a mixed way of JS and VBS. When calling, you can put it in a form and use the following statement:
<SCRIPT Language="JavaScript" SRC="surveycode.asp?id=1"></SCRIPT>
Follow the above process, before displaying the form , first determine whether the investigation exists and is in progress. In addition, a hidden parameter must be submitted in the form to represent the question number (id) of the survey. When the answer is submitted, the answer number vote_no
file name surveycode.asp
<!--#include file="inc. asp" -->
<%
id=request.querystring("id")
if id<>"" then ''if there are parameters
opendb my '' connect to the database
sql="select * from survey where survey_id="& id ''query statement
searchtable my,sql,rs ''Query database
if not rs.eof then ''If there is this investigation record
question=rs("survey_question") ''Read the question
surveytype=rs("survey_type") ''Read the answer type
stime=rs("survey_stime") ''Read the start time
etime=rs("survey_etime") ''Read the end time
closetable rs ''Close table
if stime<now() and etime>now() then ''if investigation is in progress
''Output the survey form below
''First output the form and questions, then submit the form to survey_vote.asp
%>
document.write("<form action=''survey_vote.asp'' target=''_blank'' method=''post''>");
document.write("<table border=''1'' cellpadding=''2'' cellspacing=0'' bordercolorligh=''#000000''");
document.write(" bordercolordark=''#ffffff'' width=''100%'' align=''center''><tbody>");
document.write("<tr><td colspan=''2'' align=''center''><b><%=server.htmlencode(question)%></b></td></tr >");
<%
sql="select vote_no,vote_answer from survey_vote where vote_id="&id ''SQL to query the answer
searchtable my,sql,rs ''Execute query
if not rs.eof then ''If there is an answer, output it
for i=1 to rs.recordcount
%>
document.write("<tr><td align=''right''><input name=''res'' type=''");
<%
if surveytype then ''Judge the type, display single selection or multiple selection
%>
document.write("checkbox");
<%else%>
document.write("radio");
<%end if ''The following sentence outputs the text of the answer and the submitted value (vote_no)%>
document.write("'' value=<%=rs("vote_no")%>></td><td><%=rs("vote_answer")%></td></tr>");
<%
rs.movenext
next
''The following sentences output a hidden parameter and pass the question number (id)
'' and use a JS function to define the link after clicking to view
%>
document.write("<tr><td colspan=''2'' align=''center''><input type=''hidden'' name=''id'' value=''<%=id% >''>");
document.write("<input type=''submit'' class=button value=''vote''> ");
document.write("<input type=button class=button value=''View'' onclick=''jump(<%=id%>)''>");
document.write("</td></tr></tbody></table></form>");
function jump(id){
window.open("survey_vote.asp?id="+id,"survey")
}
<%
end if
end if
end if
closetablers
closedb my
end if
%>
After surveycode.asp is completed, we have determined the following points in implementation:
1. In survey_vote.asp, if the querystring parameter id has a value, the result is viewed;
2. In survey_vote.asp, if the form parameter If the id has a value, statistics must be performed first;
3. In survey_vote.asp, the submitted form parameter res is the answer number vote_no;
7. Statistical results
First, we will complete the display of statistical results survey_vote, which is most closely related to surveycode.asp .asp files. At the end of the previous article, we have explained some parameters determined in surveycode.asp.
Statistical results survey_vote.asp
<!--#include file="inc.asp" -->
<html>
<head>
<title>Survey statistics</title>
<link rel="stylesheet" href="main.css" type="text/css">
</head>
<body>
<%
''In the previous sentence, first add the include file and reference the function.
id=request.querystring("id") ''Get querystring parameter id
opendb my '' connect to the database
if id="" then ''If not, you are not looking at the result directly.
id=request.form("id") ''Get form parameter id
if id<>"" then ''If there is a value, it must be counted first
surveycount() ''Call statistical subroutine
end if
end if
if id<>"" then
disp_survey() ''No matter which it is, the result will be displayed in the end
end if
closedb my ''Close database
''-----Statistical subroutine-----
sub surveycount()
if session("survey_ok")="" then ''If you haven't voted yet
no=request.form("res") ''Get the answer number
if no<>"" then
''Define SQL statements to increase the number of submitted answers by 1
sql="update survey_vote set vote_count=vote_count+1 where vote_no= in (" & no &")"
my.execute sql
end if
session("survey_ok")="ok"
end if
end sub
''------------------
''---Display result subroutine---
sub disp_survey()
''Define SQL statements to get survey questions
sql="select survey_question from survey where survey_id=" & id
searchtable my,sql,rs ''Execute query
question=rs("survey_question") ''Save the question into question
closetable rs ''Close table
''Define the SQL statement and get the total number of answers
sql="select sum(vote_count) as total from survey_vote where vote_id="& id
searchtable my,sql,rs
total=rs("total")
closetable rs ''close table
'' defines the SQL statement and gets all the answer text parts and vote numbers
sql="select vote_answer,vote_count from survey_vote where vote_id=" & id
searchtable my,sql,rs ''Execute query
''The following uses tables to output statistical tables
%>
<table width="500" border="1" align="center" cellpadding="2" cellspacing="0"
bordercolorligh="#000000" bordercolordark="#ffffff">
<tr>
<td colspan="4" align="center"><b>Survey statistics</b></td>
</tr>
<tr>
<td colspan="4"><b>Survey question: <%=question%></b></td>
</tr>
<tr>
<td width="150" align="center" height="20">Answer</td>
<td width="150" align="center" height="20">Voter turnout</td>
<td width="100" align="center" height="20">Proportion</td>
<td width="100" align="center" height="20">Number of votes</td>
</tr>
<%do while not rs.eof
if total=0 then
percent=0 ''If no one votes, the percentage is 0
else
percent=int(rs("vote_count")/total*10000)/100 ''Calculate percentage
end if
%>
<tr>
<td width="150" align="center"><%=rs("vote_answer")%></td>
<td width="150" align="left">
<table border="0" width="<%=percent%>" bgcolor="#CCCC00" height="10">
<tr>
<td></td>
</tr>
</table>
</td>
<td width="100" align="center"><%=percent%>%</td>
<td width="100" align="center"><%=rs("vote_count")%></td>
</tr>
<%
rs.movenext
loop
%>
<tr>
<td colspan="4"> There are <%=total%> votes as of <%=now()%>
<a href="javascript:window.close()">Close window</a>
</td>
</tr>
</table>
<%
closetable rs ''Close table
end sub
''------------------
%>
</body>
</html>
In the process of displaying the vote, we use the session variable survey_ok to indicate whether the vote has been cast. In addition, this display statistics refers to CSS files to control the style of the table. You can add it yourself according to your own requirements.
8. List the status of all surveys
Now let's complete survey.asp. Its main task is to list the status of all surveys, including:
1. Survey questions, linked to the voting form page (written directly on this page);
2. The start time of the survey;
3. The end time of the survey;
4. The status of the survey: not started, in progress, ended;
5. The number of votes in the survey;
6. The type of survey, single choice or multiple choice;
7. Also provide a link to view the voting results;
according to these requirements, just query the corresponding table. Some statements, such as getting the total number of votes, the SQL statements have actually been written in survey_vote.asp above.
List the status of all surveys survey.asp
<!--#include file="inc.asp" -->
<html>
<head>
<title>Online survey list</title>
<link rel="stylesheet" href="main.css" type="text/css">
</head>
<body>
<%
id=request.querystring("id") ''Get parameters
if id<>"" then ''If there are parameters, display this survey form
response.write "<SCRIPT Language=''JavaScript'' SRC=''surveycode.asp?id="&id&"''></SCRIPT>"
else '' Otherwise, call the subroutine to display the status
disstat()
end if
''-----Display status subroutine----
sub disstat()
opendb my '' connect to the database
opentable my,"survey",rs ''Open the table directly
''The following uses a table to display each record
''Show header first
%>
<table width="760" border="1" cellspacing="0" cellpadding="2"
align="center" bordercolorligh="#000000" bordercolordark="#ffffff">
<tr>
<td colspan="8" align="center"><b>Online survey list</b></td>
</tr>
<tr>
<td width="50" align="center" height="20">Number</td>
<td width="200" align="center" height="20">Survey questions</td>
<td width="50" align="center" height="20">Type</td>
<td width="140" align="center" height="20">Start time</td>
<td width="140" align="center" height="20">End time</td>
<td width="50" align="center" height="20">Status</td>
<td width="80" align="center" height="20">Number of votes cast</td>
<td width="50" align="center" height="20">View</td>
</tr>
<%
''Output each record below
do while not rs.eof
''Read each field first
id=rs("survey_id")
question=rs("survey_question")
''Read type
if rs("survey_type") then
stype="Multiple selection"
else
stype="single choice"
end if
stime=rs("survey_stime")
etime=rs("survey_etime")
''Judgment status
if now()<stime then
stat="not started"
else
if now<etime then
stat="in progress"
else
stat="Ended"
end if
end if
''Define the SQL statement and get the total number of answers
sql="select sum(vote_count) as total from survey_vote where vote_id="& id
searchtable my,sql,tmprs ''query
total=tmprs("total")
closeable tmprs ''Close table
''Output a record below
%>
<tr>
<td align="center" height="20"><%=id%></td>
<td height="20">
<a href="survey.asp?id=<%=id%>"><%=question%></a>
</td>
<td align="center" height="20"><%=stype%></td>
<td align="center" height="20"><%=stime%></td>
<td align="center" height="20"><%=etime%></td>
<td align="center" height="20"><%=stat%></td>
<td align="center" height="20"><%=total%></td>
<td align="center" height="20">
<a href="survey_vote.asp?id=<%=id%>" target="_blank">View</a>
</td>
</tr>
<%
rs.movenext ''Move to the next item, loop
loop
%>
</table>
<%
closetable rs ''Close table
closedb my ''Close the database
end sub
''----------------------
%>
</body>
</html>
9. Backend Management
In the backend management page survey_manage.asp, we have already listed the management functions it wants to achieve. The management process is to first display all investigations. For investigations that have not yet started, you can modify or delete them; for investigations that have ended, you can delete them but cannot modify them; for ongoing investigations, you can only modify their end time. Use one parameter action to represent the action, the meaning is as follows:
1. No parameters. Indicates the first time to enter, display the login form
2, login means to execute the login
3, logout means to execute the logout
4, showaddquestion means to display an additional survey
5, showsurvey means to display a survey
6, doaddsurvey means to execute the addition of a survey
7, doaddanswer means to execute Add an answer
8. dodelsurvey means delete a survey
9. dodelanswer means delete an answer
10. domodify means modify a survey and answer