Example : Node Js codes to fetch specific records from Xampp’s MySql database and fill them into respective UI elements/boxes.
student.ejs (inside the views folder)
<!DOCTYPE html>
<html lang="en">
<head>
<title><%= title %></title>
<script>
function resetForm()
{
const form = document.getElementById("studentForm");
form.reset();
form.querySelectorAll("input, textarea, select").forEach(el =>
{
if (el.type === "checkbox" || el.type === "radio")
{
el.checked = false;
}
else
{
el.value = "";
}
});
}
</script>
</head>
<body>
<center><fieldset style="width: 650px;">
<h1>Student Registration</h1>
<% if (message) { %>
<p style="color: green;"><%= message %></p>
<% } %>
<!-- Email dropdown to fetch student -->
<form method="GET" action="/student">
<select name="search2" onchange="this.form.submit()">
<option value="">Choose Email</option>
<% emails.forEach(function(email) { %>
<!-- <option value="<%= email %>" <%= student2.email5 === email ? "selected" : "" %>><%= email %></option> -->
<option value="<%= email %>" <%= (student2 && student2.email5 === email) ? "selected" : "" %>><%= email %></option>
<% }); %>
</select>
</form>
<br>
<form id="studentForm" action="/student" method="POST">
<table>
<tr>
<td>Name</td>
<td>:</td>
<td><input type="text" name="name1" value="<%= student2.name5 || '' %>" required></td>
</tr>
<tr>
<td>Gender</td>
<td>:</td>
<td>
<input type="radio" name="gen1" value="Male" <%= student2.gen5 == "Male" ? "checked" : "" %>> Male
<input type="radio" name="gen1" value="Female" <%= student2.gen5 == "Female" ? "checked" : "" %>> Female
<input type="radio" name="gen1" value="Transgender" <%= student2.gen5 == "Transgender" ? "checked" : "" %>> Transgender
</td>
</tr>
<tr>
<td>Mobile</td>
<td>:</td>
<td><input type="number" name="mobile1" value="<%= student2.mobile5 || '' %>"></td>
</tr>
<tr>
<td>Email</td>
<td>:</td>
<td><input type="email" name="email1" value="<%= student2.email5 || '' %>"></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input type="password" name="pass1" value="<%= student2.pass5 || '' %>"></td>
</tr>
<tr>
<td>DOB</td>
<td>:</td>
<td><input type="date" name="date1" value="<%= student2.date5 || '' %>"></td>
</tr>
<tr>
<td>Time</td>
<td>:</td>
<td><input type="time" name="time1" value="<%= student2.time5 || '' %>"></td>
</tr>
<tr>
<td>Address</td>
<td>:</td>
<td><textarea name="addr1"><%= student2.addr5 || '' %></textarea></td>
</tr>
<tr>
<td>Education</td>
<td>:</td>
<td>
<input type="checkbox" name="nonmatric1" <%= student2.nonmatric5 == 1 ? "checked" : "" %>> Non-Matric
<input type="checkbox" name="matric1" <%= student2.matric5 == 1 ? "checked" : "" %>> Matric
<input type="checkbox" name="inter1" <%= student2.inter5 == 1 ? "checked" : "" %>> Inter
<input type="checkbox" name="grad1" <%= student2.grad5 == 1 ? "checked" : "" %>> Graduation
<input type="checkbox" name="postgrad1" <%= student2.postgrad5 == 1 ? "checked" : "" %>> PostGrad
<input type="checkbox" name="phd1" <%= student2.phd5 == 1 ? "checked" : "" %>> PhD
</td>
</td>
</tr>
<tr>
<td>Nationality</td>
<td>:</td>
<td>
<select name="nat1">
<% ["India", "USA", "Bhutan", "Sri Lanka", "Russia", "Nepal"].forEach(function(nat) { %>
<option value="<%= nat %>" <%= student2.nat5 == nat ? "selected" : "" %>><%= nat %></option>
<% }); %>
</select>
</td>
</tr>
<tr>
<td>Remarks</td>
<td>:</td>
<td><input type="text" name="rem1" value="<%= student2.rem5 || '' %>"></td>
</tr>
<tr></tr>
<tr>
<td></td>
<td></td>
<td>
<button type="submit">Submit</button>
<button type="button" onclick="resetForm()">Reset</button>
</td>
</tr>
</table>
</form>
</fieldset>
</center>
</body>
</html>
server.js
// Import required modules
const express = require("express"); // Web framework for Node.js
const mysql = require("mysql2"); // MySQL client for Node.js
const bodyParser = require("body-parser"); // Middleware to parse form data
// Create Express app
const app = express();
const PORT = 3000; // Server will run on this port
// Set EJS as the template/view engine
app.set("view engine", "ejs");
// Middleware to parse URL-encoded form data
app.use(bodyParser.urlencoded({ extended: true }));
// Serve static files from "public" folder (CSS, images, JS, etc.)
app.use(express.static("public"));
// MySQL database connection configuration
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "ejs_forms_db"
});
// Connect to MySQL database
db.connect((err) => {
if (err) {
console.error("Database Connection Failed:", err);
return;
}
console.log("Connected to MySQL database");
});
// Helper function to get all distinct emails from students table
function getAllEmails(callback) {
db.query("SELECT DISTINCT email5 FROM students", (err, results) => {
if (err) return callback(err);
const emails = results.map(row => row.email5); // Extract email5 values
callback(null, emails); // Send email list back via callback
});
}
// GET route for displaying the student form
app.get("/student", (req, res) => {
const selectedEmail = req.query.search2 || ""; // Email selected from dropdown
const message = req.query.message || ""; // Success or error message
// Load all emails to show in dropdown
getAllEmails((err, emails) => {
if (err) {
// Show error if email fetch failed
return res.render("student", {
title: "Student Form",
emails: [],
student2: {},
message: "Error loading emails"
});
}
// If an email is selected, fetch and show that student's details
if (selectedEmail) {
db.query("SELECT * FROM students WHERE email5 = ?", [selectedEmail], (err, results) => {
if (err) {
return res.render("student", {
title: "Student Form",
emails,
student2: {},
message: "Error fetching student"
});
}
// res.render("student", {
// title: "Student Form",
// emails,
// student2: results[0] || {}, // If no result, send empty object
// message: results.length ? message : "Student not found"
// });
res.render("student", {
title: "Student Form",
emails: emails || [],
student2: student2 || {}, // <-- fallback to empty object
message: message || ""
});
});
} else {
// If no email is selected, just show form with email dropdown
res.render("student", { title: "Student Form", emails, student2: {}, message });
}
});
});
// POST route to handle form submission and insert data into the database
app.post("/student", (req, res) => {
const data = req.body; // Get form data from the request body
// SQL query to insert form data into students table
const sql = `
INSERT INTO students (name5, gen5, mobile5, email5, pass5, date5, time5, addr5,
nonmatric5, matric5, inter5, grad5, postgrad5, phd5, nat5, rem5)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
// Prepare values to insert (checkboxes convert to 1 if checked, else 0)
const values = [
data.name1, data.gen1, data.mobile1, data.email1, data.pass1, data.date1, data.time1, data.addr1,
data.nonmatric1 ? 1 : 0, data.matric1 ? 1 : 0, data.inter1 ? 1 : 0, data.grad1 ? 1 : 0,
data.postgrad1 ? 1 : 0, data.phd1 ? 1 : 0, data.nat1, data.rem1
];
// Run the insert query
db.query(sql, values, (err) => {
if (err) {
console.error("Insert error:", err);
// Redirect back to form with error message
return res.redirect("/student?message=Failed to Save Data");
}
// Redirect back with success message
res.redirect("/student?message=Data Saved Successfully");
});
});
// Start the server
app.listen(PORT, () => {
console.log(`Server running on http://localhost:${PORT}`);
});
0 Comments