username在express js, mysql, react js中已经存在

Question

我试图执行发布，获取，更新和删除数据(CRUD)。到目前为止一切都很好。现在我正试图在我的CRUD网站添加一个功能，如数据已经存在于数据库(mysql)。我做了很多尝试，访问了多个网站和页面的堆栈溢出，但无法理解。谁来帮帮我吧。我想显示一个错误，如果FirstName已经存在于数据库中，而在数据库中插入新的数据。这是我的react js文件，其中我使用表单在数据库中添加数据。

AddEdit.js

import React, { useEffect } from "react";
import { useNavigate, useParams, Link } from "react-router-dom";
import "./AddEdit.css";
import axios from "axios";
import { toast } from "react-toastify";
import Container from "react-bootstrap/Container";
import Form from "react-bootstrap/Form";
import { useState } from "react";
const initialState = {
FirstName: "",
LastName: "",
FatherName: "",
userCNIC: "",
Contact: "",
Gender: "",
};
const AddEdit = () => {
const [state, setState] = useState(initialState);
const { FirstName, LastName, FatherName, userCNIC, Contact, Gender } = state;
const [gender, setGender] = useState();
const navigate = useNavigate();
const { id } = useParams();
// this useEffect will run when we have the id
// when we have the id, this basically means we are updating the content.
useEffect(() => {
axios
.get(`http://localhost:5000/api/get/${id}`)
// here we will get the response in the form of array, that will contain only one data.
// as it is fetching only single row based on the id.
.then((resp) => setState({ ...resp.data[0] }));
}, [id]);
const handleSubmit = (e) => {
var phoneno = /^d{11,12}$/;
var cnicno = /^d{12,13}$/;
// to prevent the default behavior of the browser.
// e.preventDefault();
// if (
// if all fields are not filled.
//   !FirstName ||
//   !LastName ||
//   !FatherName ||
//   !userCNIC ||
//   !Contact ||
//   !Gender
// ) {
//   toast.error("Please fill in all the input fields!");
if (!Contact.match(phoneno)) {
// checking phone validation
toast.error("Please provide correct phone number!");
e.preventDefault();
} else if (!userCNIC.match(cnicno)) {
// checking phone validation
toast.error("Please provide correct CNIC Number!");
e.preventDefault();
} else {
// if user has no id, it means it is only adding the record.
if (!id) {
axios
.post("http://localhost:5000/api/post", {
FirstName,
LastName,
FatherName,
userCNIC,
Contact,
Gender,
})
// once user is successfully able to pass the data to database, then clear the each input field.
.then(() => {
setState({
FirstName: "",
LastName: "",
FatherName: "",
userCNIC: "",
Contact: "",
Gender: "",
});
})
.catch((err) => toast.error(err.response.data));
toast.success("Contact added Successfully");
} else {
// else it is updating the record.
axios
.put(`http://localhost:5000/api/update/${id}`, {
FirstName,
LastName,
FatherName,
userCNIC,
Contact,
Gender,
})
.then(() => {
setState({
FirstName: "",
LastName: "",
FatherName: "",
userCNIC: "",
Contact: "",
Gender: "",
});
})
.catch((err) => toast.error(err.response.data));
toast.success("Contact Updated Successfully");
}
// when all the data is submitted, navigate to the home page.
setTimeout(() => navigate("/"), 500);
}
};
const handleInputChange = (e) => {
const { name, value } = e.target;
setState({ ...state, [name]: value });
};
return (
<div style={{ marginTop: "50px" }}>
<h1>Enter Student Details</h1>
<Container>
<Form onSubmit={handleSubmit} className="form">
<Form.Group controlId="form.Name">
<Form.Label>Name</Form.Label>
<Form.Control
type="text"
placeholder="Enter name"
name="FirstName"
onChange={handleInputChange}
value={FirstName || ""}
id="FirstName"
/>
</Form.Group>
<Form.Group controlId="form.Name">
<Form.Label>last name</Form.Label>
<Form.Control
type="text"
id="LastName"
name="LastName"
placeholder="Last Name"
value={LastName || ""}
onChange={handleInputChange}
/>
</Form.Group>
<Form.Group controlId="form.Name">
<Form.Label>father name</Form.Label>
<Form.Control
type="text"
id="FatherName"
name="FatherName"
placeholder="Fathers name:"
value={FatherName || ""}
onChange={handleInputChange}
/>
</Form.Group>
<Form.Group controlId="form.Name">
<Form.Label>CNIC name</Form.Label>
<Form.Control
type="text"
id="userCNIC"
name="userCNIC"
placeholder="CNIC:"
value={userCNIC || ""}
onChange={handleInputChange}
/>
</Form.Group>
<Form.Group controlId="form.Name">
<Form.Label>Contact name</Form.Label>
<Form.Control
type="text"
id="Contact"
name="Contact"
placeholder="Contact number"
value={Contact || ""}
onChange={handleInputChange}
/>
</Form.Group>
<Form.Group controlId="form.Name">
<Form.Label>Gender name</Form.Label>
<Form.Control
type="text"
id="Gender"
name="Gender"
placeholder="Gender:"
value={Gender || ""}
onChange={handleInputChange}
/>
</Form.Group>
<input type="submit" value={id ? "Update" : "Save"} />
<Link to="/">
<input type="button" value="Go Back" />
</Link>
</Form>
</Container>
</div>
);
};
export default AddEdit;

这是index.js文件。

const express = require("express");
const app = express();
const mysql = require("mysql2");
const bodyParser = require("body-parser");
const cors = require("cors"); // used to access our backend api to the react frontend.
app.use(cors());
app.use(express.json());
app.use(bodyParser.urlencoded({ extended: true }));
const db = mysql.createPool({
host: "localhost",
user: "root",
password: "MySqlRoot1012",
database: "assignment_4",
});
// to get all the data from database.
app.get("/api/get", (req, res) => {
const sqlGet = "SELECT * FROM emp_table";
db.query(sqlGet, (error, result) => {
res.send(result);
});
});
// to add the record in the database.
app.post("/api/post", (req, res) => {
// values we will pass from the front end and recieve in the req.body.
const { FirstName, LastName, FatherName, userCNIC, Contact, Gender } =
req.body;
const sqlInsert =
"INSERT INTO emp_table (FirstName, LastName, FatherName, userCNIC, Contact, Gender) VALUES (?, ?, ?, ?, ?, ?)";
db.query(
sqlInsert,
[FirstName, LastName, FatherName, userCNIC, Contact, Gender],
(error, result) => {
if (error) {
console.log(error);
} else {
res.send("Values inserted!");
}
}
);
});
app.delete("/api/remove/:id", (req, res) => {
// req.params for the route parameters,not for the data.
const { id } = req.params;
const sqlRemove = "DELETE FROM emp_table WHERE id = ?";
db.query(sqlRemove, id, (error, result) => {
if (error) {
console.log(error);
}
});
});
app.get("/api/get/:id", (req, res) => {
const { id } = req.params;
const sqlGet = "SELECT * FROM emp_table WHERE id = ?";
db.query(sqlGet, id, (error, result) => {
if (error) {
console.log(error);
}
res.send(result);
});
});
app.put("/api/update/:id", (req, res) => {
const { id } = req.params;
const { FirstName, LastName, FatherName, userCNIC, Contact, Gender } =
req.body;
const sqlUpdate =
"UPDATE emp_table SET FirstName = ?, LastName = ?, FatherName = ?, userCNIC = ?, Contact = ?, Gender = ? WHERE id = ?";
db.query(
sqlUpdate,
[FirstName, LastName, FatherName, userCNIC, Contact, Gender, id],
(error, result) => {
if (error) {
console.log(error);
}
res.send(result);
}
);
});
// app.get("/", (req, res) => {
//   const sqlInsert =
//     "INSERT INTO emp_table (emp_FirstName, LastName, FatherName, userCNIC, Contact, Gender) VALUES ('majid', 10000, '[e-mail]', '01212312312')";
//   db.query(sqlInsert, (error, result) => {
//     console.log("error", error);
//     console.log("result", result);
//     res.send("Hello World!");
//   });
// });
app.listen(5000, () => {
console.log("Server is running on the port 5000.");
});

Answer 1

在您的模式中，如果FirstName是Unique列，那么您可以从callback捕获正确的错误并返回错误消息。

否则，您需要手动检查，如

const sqlGet = "SELECT id FROM emp_table WHERE Firstname = ?";
db.query(sqlGet, firstName, (error, result) => {
if (result) {
// Show the error the Firstname already exist
} else {
// Insert the record
}  
});

Answer 2

分离应用程序的关注点。让后端告诉您FirstName是否存在。为了简化，您的前端应该只执行POST请求，并等待响应状态来知道它是否成功。然后，您的REST可以发送一条消息和状态码，以便您的前端可以显示它。例如:

const POST_PARAMS = requestBody => ({
'method': 'POST',
'headers': {
'Content-Type': 'application/json'
//auth headers, tokens etc. go here
},
'body': JSON.stringify(requestBody)
})

const register = async ({username, password}) => {
try{
let response = await fetch('https://your-rest-url.com/register', POST_PARAMS({username, password}))
let json = await response.json()
if(response.status !== 200){
// here your REST caught something bad
//handle it
// maybe json.message
return
}
//here everything is fine
// handle it again
// maybe a list with all the users to update a state or something
}catch(err){
//here something went wrong in your code
// e.g wrong parsing, something is undefined etc.
// handle it
}
}

至于查询，这取决于您使用的ORM。核心逻辑是，首先根据从请求中收到的firstName执行选择，如果找到一行，则不继续，如果没有找到记录，则继续插入。

例如:

const try_insert_user = async firstName => {
//here you will find pseudo code that depends on your db driver
//this is the knex way. check your driver's docs
let exists = await db.select('schema.table').where('firstName', firstName).first()
if(exists) throw new Error("User already exists!")
//here you can insert the user normally
}

那么在你的路由中你可以有一些像

router.post('/register', async (req, res) => {
try{
let {firstName} = req.body
await try_insert_user(firstName)
res.status(200).send({message: 'User inserted successfully!'})
}catch(err){
//this part will catch the error thrown if the user exists
res.status(400).send({message: err.message})
}
})

以上都是伪代码，当然可以给您指明方向，但当然您必须按照自己的想法处理您的情况。