node.js+MySql数据库+HTML实现内部查重系统
实现目标:
1、将每个人的项目登记,存入MySql数据库
2、在填写时进行实时正则判断,判断每项输入格式是否正确,方便之后进行查重对比
3、设置查询按钮,可以在填写提交自己的项目之前看到大家的提交详情
4、提交时,在数据库中进行姓名对比,每人只能提交一次,不能重复提交
5、提交时,项目网址与项目中文名会在MySql数据库中进行查询对比,双重保险,避免只查询一项造成漏网之鱼
6、提交完成后自动跳转到查询页面
7、提交填写页面与服务端使用form表单通信,method=post
8、提交成功跳转查询页面使用ajax从数据库获取数据动态生成
实现效果:
填写界面
正则验证
正则验证通过
当项目与别人重复时,或重复提交时会显示如下
当项目没有重复成功写入数据库和点击查询按钮时会跳转到如下界面
为方便阅读CSS代码写在文章最后
html代码部分:
1、index.html查询网站首页:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<link rel="stylesheet" href="./css/signIn.css" />
</head>
<body>
<form action="http://10.9.72.220:4002" method="POST">
<h2>项目查重系统</h2>
<div>
<label for="name">姓 名:</label>
<input type="text" id="name" name="name" required/>
<span></span>
</div>
<div>
<label for="website">网 址:</label>
<input type="text" id="website" name="website" required/>
<span></span>
</div>
<div class="div1">
<label for="chsite">网站中文名称:</label>
<input type="text" id="chsite" name="chsite" required/>
<span></span>
</div>
<div>
<input type="submit" />
<input id="sear" type="button" value="查询" />
</div>
</form>
<div>
<div class="shuoming">
<h3>项目查重系统V1.9.3使用说明</h3>
<p>1、每个人只能插入一遍,不能修改,修改请联系管理员</p>
<p>2、请在网址中输入官网首页网址,例如百度 http://www.baidu.com</p>
<p>3、网站中文名称请填写对应官网网站全称</p>
<p>4、填写完成后会有详情页可以查看提交的项目</p>
</div>
</div>
<script type="module">
var sear = document.querySelector("#sear");
var form = document.querySelector("form");
var name = document.querySelector("#name");
var website = document.querySelector("#website");
var chsite = document.querySelector("#chsite");
import regForms from "./js/regForms.js";
init();
function init() {
name.addEventListener("input",inputHanlder);
website.addEventListener("input",inputHanlder);
chsite.addEventListener("input",inputHanlder)
form.addEventListener("submit",submitHandler)
sear.addEventListener("click",clickHandler)
}
function clickHandler(){
location.href="http://10.9.72.220:4010/show.html"
}
function inputHanlder(e){
regForms.inputVerify(this, this.nextElementSibling);
}
function submitHandler(e){
if(!regForms.allFormVerify(form)) e.preventDefault();
}
</script>
</body>
</html>
2、show.html 点击查询按钮与项目写入数据库成功时跳转页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>展示界面</title>
<style>
table {
border-collapse: collapse;
width: 100%;
}
td,
th {
border: 1px solid #000000;
text-align: center;
}
</style>
</head>
<body>
<div>
<table>
<tr>
<th>pid</th>
<th>name</th>
<th>website</th>
<th>chsite</th>
</tr>
</table>
</div>
<script>
var obj;
var table = document.querySelector("table");
console.log(table);
function ajax(data, type = "GET") {
var url = "http://10.9.72.220:4002";
var str = "";
for (var prop in data) {
str += prop + "=" + data[prop] + "&";
}
str = str.slice(0, -1);
return new Promise(function (resolve, reject) {
if (type.toLowerCase() === "get") {
url += "?" + str;
}
var xhr = new XMLHttpRequest();
xhr.open(type, url);
xhr.send(type.toLowerCase() === "get" ? null : str);
xhr.onload = function () {
resolve(xhr.response);
};
xhr.onerror = function () {
reject();
};
});
}
ajax({ type: "getAllData" })
.then((res) => render(res))
.catch((err) => console.log("err"));
function render(res) {
obj = JSON.parse(res);
rand(obj);
}
function rand() {
obj.forEach((elem) => {
console.log(elem);
var tr = document.createElement("tr");
for (var prop in elem) {
var td = document.createElement("td");
td.textContent = elem[prop];
console.log(td);
tr.appendChild(td);
}
table.appendChild(tr);
});
}
</script>
</body>
</html>
postOrgetserver.js前端与服务端通信数据处理
var http = require("http");
var querystring = require("querystring");
var server = http.createServer(function (req, res) {
var data = "";
res.writeHead(200, {
"content-type": "text/html;charset=utf-8",
});
req.on("data", function (_data) {
data += _data;
});
req.on("end", function () {
if (req.method.toLowerCase() === "get") {
if (req.url.includes("favicon.ico")) return res.end();
data = req.url.includes("?") ? req.url.split("?")[1] : "";
}
try {
data = JSON.parse(data);
} catch (e) {
data = data ? querystring.parse(data) : {};
}
console.log(data, req.method);
res.write(JSON.stringify(data));
res.end();
});
});
server.listen(4002, "10.9.72.220", function () {
console.log("服务器开启成功");
});
serverMysql.js服务端与MySql数据库通信
var http = require("http");
var querystring = require("querystring");
var mysql = require("mysql");
const { Script } = require("vm");
var server, req, res;
var data = "";
var sql = mysql.createConnection({
host: "localhost",
port: 3306,
user: "root",
password: "root",
database: "check",
});
sql.connect(function (error) {
if (error) {
console.log("数据库连接失败");
} else {
console.log("数据库连接成功");
createServer();
}
});
function createServer() {
server = http.createServer(createChannel);
server.listen(4002, "10.9.72.220", listenHandler);
}
function listenHandler() {
console.log("服务开启");
}
function createChannel(_req, _res) {
req = _req;
res = _res;
data = "";
res.writeHead(200, {
"content-type": "text/html;charset=utf-8",
"Access-Control-Allow-Origin": "*",
});
req.on("data", function (_data) {
data += _data;
});
req.on("end", channelEndHandler);
}
function channelEndHandler() {
if (req.method.toLowerCase() === "get") {
if (req.url.includes("favicon.ico")) return res.end();
data = req.url.includes("?") ? req.url.split("?")[1] : "";
}
try {
data = JSON.parse(data);
} catch (e) {
data = data ? querystring.parse(data) : {};
}
console.log(data);
if (data.type === "getAllData") {
getAllData();
return;
}
var str = "SELECT `name`,`website`,`chsite` FROM `xietian` WHERE 1";
sql.query(str, function (err, res) {
var res = JSON.stringify(res);
var bool = res.indexOf(data.name);
var web = res.indexOf(data.website);
var webs = res.indexOf(data.chsite);
console.log(webs,data.chsite);
sqlCallBack(err, res, bool, web,webs);
if (bool == -1 && (web == -1 && webs==-1)) insert();
});
function insert() {
var str =
"INSERT INTO `xietian`(`name`, `website`, `chsite`) VALUES (?,?,?)";
sql.query(str, [data.name, data.website, data.chsite], sqlCallBack);
}
}
function sqlCallBack(error, result, bool, web,webs) {
console.log(error, result);
if (web !== -1 ||webs!==-1) return res.end("你的项目与别人重复了");
else {
if (bool !== -1) {
return res.end("你不能重复插入");
} else if (error) {
res.end("插入数据失败!");
return;
} else {
res.write(
"<script>" +
"var t = 10;function openwin() {t -= 1;if(t==0){location.href='http://10.9.72.220:4010/show.html';}setTimeout('openwin()', 300);}openwin();" +
"</script>"
);
}
}
res.end("插入数据成功,3秒钟后自动跳转");
}
function getAllData() {
var str = "SELECT * FROM `xietian` WHERE 1";
sql.query(str, function (err, result) {
console.log(result);
res.end(JSON.stringify(result));
});
}
regForms.js正则表单验证
export default class regForms {
static INFO = {
name: {
reg: "^[\\u4e00-\\u9fd5]{2,4}$",
flag: "",
msg: "名字必须是中文,2-4位",
},
website: {
reg:
"^([hH][tT]{2}[pP]:\\/\\/|[hH][tT]{2}[pP][sS]:\\/\\/|w{3})+(\\.)?(([A-Za-z0-9-~]+)\\.)+([A-Za-z0-9-~\\/])+$",
flag: "",
msg: "请输入http://开头或https://开头或www开头的官网首页网址",
},
chsite: {
reg: "^([\\u4e00-\\u9fd5]|\\w|\\d){2,20}$",
flag: "",
msg: "请输入官网中文名",
},
};
static verifyReg(key, value) {
var regs=new RegExp(regForms.INFO[key].reg,regForms.INFO[key].flag);
return regs.test(value) ? "" : regForms.INFO[key].msg;
}
static setFormStyle(elem,info,key, value) {
var msg=regForms.verifyReg(key, value);
if (msg) {
elem.style.border = "1px solid #FF0000";
elem.style.outline = "1px solid #FF0000";
elem.focus();
info.textContent=msg;
return false;
}
elem.style.border = "1px solid #00FF00";
elem.style.outline = "none";
info.textContent="";
return true;
}
static allFormVerify(form){
var fd = new FormData(form);
for (var [key, value] of fd) {
var elem=document.getElementsByName(key)[0];
var info=elem.nextElementSibling;
// if (!elem.getAttribute("required")) continue;
if (!regForms.setFormStyle(elem,info,key, value)) return false;
}
return true;
}
static inputVerify(input,span){
if (input.ids) return;
input.ids = setTimeout(() => {
clearTimeout(input.ids);
input.ids = 0;
regForms.setFormStyle(input,span,input.getAttribute("name"), input.value);
}, 500);
}
}
signin.css
* {
margin: 0;
padding: 0;
}
form {
width: 800px;
margin: auto;
margin-top: 50px;
border: 1px solid #000000;
}
form > h2 {
text-align: center;
}
form input {
margin-left: 20px;
font-size: 25px;
margin-bottom: 20px;
}
form > div {
margin-top: 20px;
margin-left: 200px;
font-size: 25px;
}
.div1{
margin-left: 125px;
}
form > div:last-of-type {
margin-top: 20px;
margin-bottom: 20px;
text-align: center;
margin-left: 0;
}
form > div:last-of-type > input:last-of-type {
margin-left: 100px;
}
span{
display: block;
font-size: 16px;
color: red;
margin-left: 105px;
}
.div1 span{
margin-left: 180px;
}
.shuoming{
width: 500px;
margin: 0 auto;
margin-top: 20px;
}
.shuoming p{
margin-top: 10px;
width: 800px;
}