Gallery
Share
Explore

f23 MAD 6135 Project Template (Lab test 2)

For Lab Test 2, it is only required to submit the solution for connect to CES.db and display the record the records in it to the Console. Update to part 2. Backend Setup with Express and SQLite:

megaphone

Lab test 2 Learning outcomes:

Connecting html form to routes in your backend node JS program for the purpose of allowing user to enter information into the HTML form which can be conveyed via Ajax and Microservers to the controller partition, and used for sql (or mongodb) queries.

Once you have done this Lab:
Put all your code into a Word Document: Name it as studentName_StudentID.docx and into this Word Doc put some Screen shots of your Working Web Application
— OR — you can make a ZIP file of your Node Project Directory — name the zip file as StudentName_StudentID.zip and upload to :
⁠
https://www.dropbox.com/request/XWuVpxKDitF6wEOth14D
⁠
Note: Send either a Zip file or a Word Document.

What more do you need to do for your project?
Present your html front end and sqlite database to present a theme.
CSS: more visually appealing

Pre-requisite: The Lab Worksheet for using sqlite in Node.js

Learning Outcomes:
Make a full stack javaScript application with completely documented code.
Recall for your hand in project: I want to see very complete documentation in Markdown and GitBook: Some UML diagrams.
Your Project FS application will use a backend database which could be:
sqlite
MongoDB
Firebase
in the backend and presents an html form which user enters data into the form which is stored in the sqlite database.

Outline:

Setting Up the Environment
Installing Node.js
Setting up a new Node project
Installing necessary libraries
Backend Setup with Express and SQLite
Setting up the Express server
Integrating SQLite
Creating a database table
Frontend Setup
Creating an HTML form
Connecting the form to the backend
Running and Testing the Application

1. Setting Up the Environment:

Installing Node.js:

Download and install Node.js from
nodejs.org
⁠
.

Setting up a new Node project:

bashCopy code
mkdir fullstack-js-app
cd fullstack-js-app
npm init -y

Installing necessary libraries:

bashCopy code
npm install express sqlite3 body-parser

2. Backend Setup with Express and SQLite:

For the initial walk-through of this, I will be using the CES.db college enrollment system database we developed with dbbrowserforsql.

Setting up the Express server (server.js):

Note: This code shows you an in-memory database. For your Project, use a persistent file (file on the file system of the server).
const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();

const app = express();
const PORT = 3000;

// Middleware to parse POST data
app.use(bodyParser.urlencoded({ extended: true }));

// Database setup
let db = new sqlite3.Database(':memory:', (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to the in-memory SQlite database.');
});

// Create table
db.run('CREATE TABLE user_data (name TEXT, email TEXT)', (err) => {
if (err) {
return console.error(err.message);
}
});

app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});

app.post('/submit', (req, res) => {
const { name, email } = req.body;
db.run(`INSERT INTO user_data(name, email) VALUES(?, ?)`, [name, email], (err) => {
if (err) {
return console.error(err.message);
}
res.send('Data saved successfully!');
});
});

app.listen(PORT, () => {
console.log(`Server is running on http://localhost:${PORT}`);
});


CES Database:
⁠
https://www.dropbox.com/scl/fi/8br0nxx9tx5lkrf6frqao/ces.db?rlkey=y8p8nm14rwujhu4340q01rrha&dl=0
⁠
⁠
⁠
image.png
⁠
⁠

3. Frontend Setup:

Creating an HTML form (index.html):

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Full Stack JS App</title>
</head>
<body>
<form action="/submit" method="post">
<label for="name">Name:</label>
<input type="text" id="name" name="name" required>
<br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" required>
<br>
<input type="submit" value="Submit">
</form>
</body>
</html>


megaphone

Version E:


You have three tables - classes, enrollments, and students.
For the context of the application you provided, we'd like to modify the existing behavior of saving the user (probably a student in this context) into the database, and allow a student to enroll in a class.
Let's create some basic routes to:
List all classes
Enroll a student in a class
Register a new student
Here's how you can modify your index.js to handle these:

const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();

const app = express();
const PORT = 3000;

console.log("Starting server...");

// Middleware to parse POST data
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json()); // to parse JSON data

// Middleware to serve static files from the 'public' directory
app.use(express.static('public'));

// Database setup
const db = new sqlite3.Database('./ces.db', (err) => {
if (err) {
console.error("Error connecting to the ces SQLite database:", err.message);
return;
}
console.log('Connected to the ces SQLite database.');
});

// Route to serve index.html
app.get('/', (req, res) => {
res.sendFile(__dirname + '/public/index.html');
});

// Route to register a new student
app.post('/register', (req, res) => {
const { studentname } = req.body;
db.run(`INSERT INTO students(studentname) VALUES(?)`, [studentname], (err) => {
if (err) {
console.error("Error registering student:", err.message);
res.status(500).send('An error occurred while registering student.');
return;
}
res.send('Student registered successfully!');
});
});

// Route to list all classes
app.get('/classes', (req, res) => {
db.all(`SELECT * FROM classes`, [], (err, rows) => {
if (err) {
console.error("Error fetching classes:", err.message);
res.status(500).send('An error occurred while fetching classes.');
return;
}
res.json(rows);
});
});

// Route to enroll a student in a class
app.post('/enroll', (req, res) => {
const { studentid, classid } = req.body;
db.run(`INSERT INTO enrollments(studentid, classid) VALUES(?, ?)`, [studentid, classid], (err) => {
if (err) {
console.error("Error enrolling student:", err.message);
res.status(500).send('An error occurred while enrolling the student.');
return;
}
res.send('Student enrolled successfully!');
});
});

app.listen(PORT, () => {
console.log(`Server is running on http://localhost:${PORT}`);
});

With these changes, the application can now handle registering students, enrolling them in classes, and listing available classes.
The routes and methods (GET/POST) indicate the kind of operations that can be performed, and each route is associated with the respective table in the ces.db database.

To make the system work end-to-end, you'll need to modify or create the index.html file to handle these actions, by sending requests to the server when the user interacts with the page (like submitting forms).

minus

Updatedindex.html with interactions for registering students, enrolling them in classes, and listing available classes.


<!DOCTYPE html>
<html lang="en">

<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CES Party</title>
<style>
body {
font-family: Arial, sans-serif;
background-color: #FFE082;
background-image: linear-gradient(45deg, #FFD54F, #FF6E40);
animation: gradientBG 5s infinite linear alternate;
}

@keyframes gradientBG {
0% {
background-image: linear-gradient(45deg, #FFD54F, #FF6E40);
}
100% {
background-image: linear-gradient(45deg, #FF6E40, #FFD54F);
}
}

.container {
max-width: 600px;
margin: 2rem auto;
background-color: rgba(255, 255, 255, 0.9);
padding: 2rem;
border-radius: 15px;
}

h2 {
text-align: center;
color: #3949AB;
}

button {
background-color: #3949AB;
color: white;
border: none;
padding: 10px 15px;
border-radius: 5px;
cursor: pointer;
transition: background-color 0.3s;
}

button:hover {
background-color: #303F9F;
}

ul {
list-style-type: none;
}
</style>
</head>

<body>

<div class="container">
<h2>Register Student</h2>
<form id="registerForm">
<label for="studentname">Student Name:</label>
<input type="text" id="studentname" name="studentname" required>
<button type="submit">Register</button>
</form>

<h2>Enroll in Class</h2>
<form id="enrollForm">
<label for="studentid">Student ID:</label>
<input type="text" id="studentid" name="studentid" required>

<label for="classid">Class ID:</label>
<input type="text" id="classid" name="classid" required>

<button type="submit">Enroll</button>
</form>

<h2>Available Classes</h2>
<button id="fetchClasses">Show Classes</button>
<ul id="classesList"></ul>
</div>

<script>
document.getElementById('registerForm').addEventListener('submit', function (e) {
e.preventDefault();
let studentname = e.target.studentname.value;
fetch('/register', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({ studentname })
}).then(res => res.text()).then(alert);
});

document.getElementById('enrollForm').addEventListener('submit', function (e) {
e.preventDefault();
let studentid = e.target.studentid.value;
let classid = e.target.classid.value;
fetch('/enroll', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({ studentid, classid })
}).then(res => res.text()).then(alert);
});

document.getElementById('fetchClasses').addEventListener('click', function () {
fetch('/classes').then(res => res.json()).then(data => {
let ul = document.getElementById('classesList');
ul.innerHTML = '';
data.forEach(item => {
let li = document.createElement('li');
li.textContent = `Class ID: ${item.classid}, Name: ${item.classname}, Location: ${item.roomlocation}, Date & Time: ${item.classdatetime}`;
ul.appendChild(li);
});
});
});
</script>
</body>

</html>

This index.html contains:
Forms to register a new student and to enroll students in classes.
A button to fetch and display a list of available classes.
CSS styling with bright colors, using the "birthday party" theme. The background color animates between two bright shades.
JavaScript logic to handle form submissions and interact with the server endpoints you provided earlier.
You should place this index.html inside the public directory (as mentioned in your Node.js

4. Running and Testing the Application:

Run the server:
bashCopy code
node server.js

Visit http://localhost:3000 in your browser and test the form.


More advanced formulation: Let’s populate the form from the Database:

megaphone
You have three tables - classes, enrollments, and students.
For the context of the application you provided, we'd like to modify the existing behavior of saving the user (probably a student in this context) into the database, and maybe allowing a student to enroll in a class.
Let's create some basic routes to:
List all classes
Enroll a student in a class
Register a new student
Here's how you can modify your index.js to handle these:
javascriptCopy code
const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();

const app = express();
const PORT = 3000;

console.log("Starting server...");

// Middleware to parse POST data
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json()); // to parse JSON data

// Middleware to serve static files from the 'public' directory
app.use(express.static('public'));

// Database setup
const db = new sqlite3.Database('./ces.db', (err) => {
if (err) {
console.error("Error connecting to the ces SQLite database:", err.message);
return;
}
console.log('Connected to the ces SQLite database.');
});

// Route to serve index.html
app.get('/', (req, res) => {
res.sendFile(__dirname + '/public/index.html');
});

// Route to register a new student
app.post('/register', (req, res) => {
const { studentname } = req.body;
db.run(`INSERT INTO students(studentname) VALUES(?)`, [studentname], (err) => {
if (err) {
console.error("Error registering student:", err.message);
res.status(500).send('An error occurred while registering student.');
return;
}
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.