Example : How can I fetch all data from a specific field/column of Xampp’s MySql and feed all these into a drop-down/combo box/Hang code in a UI?
student.ejs (inside the views folder)
<!DOCTYPE html>
<html lang="en">
<head>
<title><%= title %></title>
</head>
<body>
<center>
<fieldset style="width: 700px;">
<h1>Student Registration</h1>
<a href="/">Home</a> | <a href="/employee">Employee</a> | <a href="/customer">Customer</a>
<% if (message) { %>
<p style="color: green;"><%= message %></p>
<% } %>
<form action="/student" method="POST">
<table>
<tr>
<td>Name</td>
<td>:</td>
<td><input type="text" name="name1" required></td>
<td>
<select name="ddsearch">
<option value="Choose One">Choose One</option>
<% emails.forEach(function(email) { %>
<option value="<%= email %>"><%= email %></option>
<% }); %>
</select>
</td>
</tr>
<tr>
<td>Gender</td>
<td>:</td>
<td>
<input type="radio" name="gen1" value="Male">Male
<input type="radio" name="gen1" value="Female">Female
<input type="radio" name="gen1" value="Transgender">Transgender
</td>
</tr>
<tr>
<td>Mobile No</td>
<td>:</td>
<td><input type="number" name="mobile1"></td>
</tr>
<tr>
<td>Email</td>
<td>:</td>
<td><input type="email" name="email1"></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input type="password" name="pass1"></td>
</tr>
<tr>
<td>DOB</td>
<td>:</td>
<td><input type="date" name="date1"></td>
</tr>
<tr>
<td>Time</td>
<td>:</td>
<td><input type="time" name="time1"></td>
</tr>
<tr>
<td>Address</td>
<td>:</td>
<td><textarea rows="4" cols="30" name="addr1"></textarea></td>
</tr>
<tr>
<td>Education</td>
<td>:</td>
<td>
<input type="checkbox" name="nonmatric1" value="Non-Matric">Non-Matric
<input type="checkbox" name="matric1" value="Matric">Matric
<input type="checkbox" name="inter1" value="Intermediate">Intermediate
<input type="checkbox" name="grad1" value="Graduation">Graduation
<input type="checkbox" name="postgrad1" value="PostGraduation">PostGraduation
<input type="checkbox" name="phd1" value="PhD">PhD
</td>
</tr>
<tr>
<td>Nationality</td>
<td>:</td>
<td>
<select name="nat1">
<option value="India">India</option>
<option value="USA">USA</option>
<option value="Bhutan">Bhutan</option>
<option value="Sri Lanka">Sri Lanka</option>
<option value="Russia">Russia</option>
<option value="Nepal">Nepal</option>
</select>
</td>
</tr>
<tr>
<td>Remarks</td>
<td>:</td>
<td><input type="text" name="rem1" value="N/A"></td>
</tr>
<tr>
<td colspan="3" align="center">
<button type="submit" name="action" value="submit">Submit</button>
<button type="reset">Reset</button>
<button type="submit" name="action" value="update">Update</button>
<button type="submit" name="action" value="delete">Delete</button>
<button type="submit" name="action" value="search">Search</button>
<button type="submit" name="action" value="exit">Exit</button>
</td>
</tr>
</table>
</form>
</fieldset>
</center>
</body>
</html>
server.js
const express = require("express");
const mysql = require("mysql2");
const bodyParser = require("body-parser");
const app = express();
const PORT = 3000;
// Set EJS as the template engine
app.set("view engine", "ejs");
// Middleware
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static("public"));
// MySQL Connection
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "ejs_forms_db"
});
db.connect((err) => {
if (err) {
console.error("Database connection failed: " + err.stack);
return;
}
console.log("Connected successfully to MySQL database");
});
// Render form with emails
function renderStudentForm(res, message = "") {
db.query("SELECT email5 FROM students", (err, results) => {
if (err) {
console.error("Error fetching emails:", err);
return res.render("student", {
title: "Student Registration",
emails: [],
message: "Error loading emails!"
});
}
const emails = results.map(row => row.email5);
res.render("student", {
title: "Student Registration",
emails,
message
});
});
}
// GET route
app.get("/student", (req, res) => {
const message = req.query.message || "";
renderStudentForm(res, message);
});
// POST route for all actions
app.post("/student", (req, res) => {
const action = req.body.action;
const {
name1, gen1, mobile1, email1, pass1,
date1, time1, addr1, nat1, rem1
} = req.body;
const nonmatric1 = req.body.nonmatric1 ? 1 : 0;
const matric1 = req.body.matric1 ? 1 : 0;
const inter1 = req.body.inter1 ? 1 : 0;
const grad1 = req.body.grad1 ? 1 : 0;
const postgrad1 = req.body.postgrad1 ? 1 : 0;
const phd1 = req.body.phd1 ? 1 : 0;
if (action === "submit") {
const sql = `INSERT INTO students
(name5, gen5, mobile5, email5, pass5, date5, time5, addr5,
nonmatric5, matric5, inter5, grad5, postgrad5, phd5, nat5, rem5)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
const values = [
name1, gen1, mobile1, email1, pass1, date1, time1, addr1,
nonmatric1, matric1, inter1, grad1, postgrad1, phd1, nat1, rem1
];
db.query(sql, values, (err) => {
if (err) {
console.error("Insert error:", err);
return res.redirect("/student?message=Error saving data!");
}
return res.redirect("/student?message=Student record added successfully!");
});
} else {
return res.redirect("/student?message=Other actions not yet implemented.");
}
});
// Start server
app.listen(PORT, () => {
console.log(`Server running at http://localhost:${PORT}`);
});
0 Comments