I was trying to perform post, get, update and delete data(CRUD). Until now everything is perfectly fine. Now I was trying to add a functionality in my CRUD website like data already exists in the database(mysql). I have done a lot of tries, visited multiple websites and pages of stack overflow but couldn't understand. Someone please help me out. I want to show an error if FirstName already exists in the database while inserting the new data in the database. This is my react js file in which I am adding the data in the database using Forms.
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;
And this is index.js file.
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, '[email protected]', '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.");
});
CodePudding user response：
In your schema, If the FirstName is a Unique column, Then you can catch the correct error from the callback and return the error message.
Otherwise, You need to check it manually like,
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
}
});
CodePudding user response：
Separate your app's concerns. Let the backend tell you whether the FirstName exists or not. To simplify, your front should only perform the POST request and wait for the response status to know whether it was successful or not. Then your REST can send a message along with the status code so your front end can show it. For example:
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
}
}
As for the queries, it depends on what ORM you are using. The core logic is that you first perform a select based on the firstName you receive from the request, and if you find a row you don't proceed, if you don't find a record you proceed with your insertion.
For example:
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
}
Then in your route you can have something like
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})
}
})
All the above is pseudo code of course that can point you to some direction, but of course you'll have to handle your cases as you deem.