I want to make the following website work as follows. The user should be able to Insert new rows into any table within the datbase. I.e. there should be three text input fields one for Table name, the other for columns names, and lastly values. Then there should be an insert button that when clicked inserts this new row into the specified table. Lastly, the database should update automatically after the insert is presses. So that the user can then go up to the database search text field and see the cahanges they have made to the database within the website. Note that for some columns the primitive type will change. I.e. some will be strings and some will be integers. Usually just those.
This is what I have so far: index.html:
<!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>DBFinal</title>
<style>
body {
font-family: Arial, sans-serif;
margin: 0;
padding: 0;
/* overflow: hidden; */
background: #F1F2B5; /* fallback for old browsers */
background: -webkit-linear-gradient(
to right,
#135058,
#F1F2B5
); /* Chrome 10-25, Safari 5.1-6 */
background: linear-gradient(
to right,
#135058,
#F1F2B5
); /* W3C, IE 10+/ Edge, Firefox 16+, Chrome 26+, Opera 12+, Safari 7+ */
overflow-y: scroll;
}
h1 {
text-align: center;
margin-top: 50px;
}
form {
text-align: center;
margin-top: 50px;
display: flex;
justify-content: center;
align-items: center;
}
label {
font-size: 18px;
font-weight: bold;
color: wheat;
text-transform: uppercase;
text-decoration: wavy;
margin-right: 10px;
}
input[type="text"] {
padding: 10px;
font-size: 16px;
border-radius: 5px;
border: none;
box-shadow: 0px 0px 5px rgba(0, 0, 0, 0.3);
margin-right: 10px;
}
button {
padding: 10px;
font-size: 16px;
border-radius: 5px;
border: none;
background-color: #4CAF50;
color: white;
box-shadow: 0px 0px 5px rgba(0, 0, 0, 0.3);
cursor: pointer;
}
table {
margin: 50px auto;
border-collapse: collapse;
width: 50%;
border: 1px solid #ddd;
font-size: 18px;
}
th,
td {
text-align: left;
padding: 12px;
}
th {
background-color: #4CAF50;
color: white;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
.container {
background-color: white;
border-radius: 10px;
box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
padding: 10px;
margin: 25px auto;
width: 50%;
text-align: center;
}
.search-container {
display: flex;
justify-content: center;
align-items: center;
margin-top: 50px;
}
nav {
display: flex;
justify-content: space-between;
align-items: center;
padding: 0px 40px;
background: #d9da9f; /* fallback for old browsers */
background: -webkit-linear-gradient(
to right,
#0f474f,
#e5e6a2
); /* Chrome 10-25, Safari 5.1-6 */
background: linear-gradient(
to right,
#0d4148,
#e0e19a
); /* W3C, IE 10+/ Edge, Firefox 16+, Chrome 26+, Opera 12+, Safari 7+ */
position: fixed;
top: 0;
right: 0;
left: 0;
z-index: 9999;
}
nav .logo {
font-size: 30px;
text-decoration: none;
color: rgb(205, 200, 200);
padding: 10px;
}
.hamburger-menu {
position: relative;
width: 30px;
height: 22.5px;
cursor: pointer;
margin-right: 10px;
}
.hamburger-menu span {
position: absolute;
width: 100%;
height: 3px;
background-color: black;
transition: all 0.3s ease-in-out;
}
.hamburger-menu span:nth-child(1) {
top: 0;
}
.hamburger-menu span:nth-child(2) {
top: 50%;
transform: translateY(-50%);
}
.hamburger-menu span:nth-child(3) {
bottom: 0;
}
.menu {
position: absolute;
top: 100%;
right: 0;
background-color: white;
border-radius: 5px;
box-shadow: 0px 0px 5px rgba(0, 0, 0, 0.3);
display: none;
padding: 10px;
margin-top: 5px;
margin-right: 20px;
}
.menu.active {
display: block;
}
.menu a {
display: block;
padding: 10px;
/* padding-left: 20px; */
font-size: 16px;
text-align: center;
text-decoration: none;
color: black;
margin-right: 20px;
}
.menu a:hover {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<nav>
<a href="#" class="logo">Simple</a>
<div class="hamburger-menu" onclick="toggleMenu()">
<span></span>
<span></span>
<span></span>
</div>
<div class="menu">
<a href="#">Info</a>
<a href="#">DB Explained</a>
</div>
</nav>
<script>
function toggleMenu()
{
const menu = document.querySelector('.menu');
menu.classList.toggle('active');
}
</script>
<div class="container">
<h1>Insert Into Table in DB</h1>
</div>
<div class="insert-container">
<label for="table-name">Table Name:</label>
<input type="text" id="table-name" /><br />
<label for="columns">Columns:</label>
<input type="text" id="columns" /><br />
<label for="values">Values:</label>
<input type="text" id="values" /><br />
<button id="insert-button">Insert</button>
</div>
<div class="search-container">
<label for="inputtedSearch">Search Table Name:</label>
<input type="text" id="inputtedSearch" />
<button id="buttonSearch">Search</button>
</div>
<table id="resultantTable"></table>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="index.js"></script>
</body>
</html>
Index.js:
jQuery(function () {
jQuery("#buttonSearch").on("click", function () {
var theTableName = jQuery("#inputtedSearch").val();
jQuery.ajax({
url: "/getTableDB",
type: "POST",
data: { theTableName: theTableName },
success: function (data) {
var headerVals = Object.keys(data[0]);
var htmlHeaderVal = "";
headerVals.forEach(function (i) {
htmlHeaderVal += "<th>" + i + "</th>";
});
jQuery("#resultantTable").html(
"<tr>" + htmlHeaderVal + "</tr>"
);
var htmlRowPlaceholder = "";
data.forEach(function (rows) {
var htmlRow = "";
headerVals.forEach(function (cols) {
htmlRow += "<td>" + rows[cols] + "</td>";
});
htmlRowPlaceholder += "<tr>" + htmlRow + "</tr>";
});
jQuery("#resultantTable").append(htmlRowPlaceholder);
},
error: function (jqXHR, textStatus, errorThrown) {
console.log("Error:", errorThrown);
},
});
});
jQuery("#insert-button").on("click", function () {
var tableName = jQuery("#table-name").val();
var columns = jQuery("#columns").val().split(",");
var values = jQuery("#values").val().split(",");
var data = {};
for (var i = 0; i < columns.length; i++) {
data[columns[i].trim()] = values[i].trim();
}
jQuery.ajax({
url: "/insertIntoTable",
type: "POST",
data: { tableName: tableName, data: data },
success: function () {
// Update the search results table
jQuery("#buttonSearch").click();
},
error: function (jqXHR, textStatus, errorThrown) {
console.log("Error:", errorThrown);
},
});
});
});
And lastly server.js:
const path = require("path");
const express = require("express");
const sqlite3 = require("sqlite3").verbose();
const bodyParser = require("body-parser");
const { data } = require("jquery");
const openPort = 5000;
const app = express();
// Serve static files in the 'public' directory
app.use(express.static(path.join(__dirname, "public")));
// Parses incoming request bodies in a middleware before our handlers, available under the req.body property.
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
// Here we define a route that serves to handle Post to /getTableDB
app.post("/getTableDB", (req, res) => {
// Garners the name of the table from the request body
const theTableName = req.body.theTableName;
// Opens the database connection
const database = new sqlite3.Database("UNBDB.db", (err) => {
if (err) {
console.error(err.message);
return res
.status(500)
.send("Error in the servers internal component");
}
console.log("Successful connection to database.");
});
// Retreive data from table
database.all(`SELECT * FROM ${theTableName}`, [], (err, rows) => {
if (err) {
console.error(err.message);
return res
.status(500)
.send("Error in the servers internal component");
}
res.send(rows);
// Close ongoing connection to database
database.close((err) => {
if (err) {
console.error(err.message);
}
console.log("Database connection closed");
});
});
});
app.post("/insertIntoTable", (req, res) => {
const tableName = req.body.tableName;
const data = req.body.data;
const database = new sqlite3.Database("UNBDB.db", (err) => {
if (err) {
console.error(err.message);
return res
.status(500)
.send("Error in the servers internal component");
}
console.log("Successful connection to database.");
});
const columns = Object.keys(data).join(", ");
const values = Object.values(data)
.map((value) => {
if (isNaN(value)) {
return "'" + value + "'";
} else {
return value;
}
})
.join(", ");
database.run(
`INSERT INTO ${tableName} (${columns}) VALUES (${values})`,
(err) => {
if (err) {
console.error(err.message);
return res
.status(500)
.send("Error in the servers internal component");
}
res.sendStatus(200);
database.close((err) => {
if (err) {
console.error(err.message);
}
console.log("Database connection closed");
});
}
);
});
// Serves the index.html file and index.js file when the root path is requested
app.get("/", function (req, res) {
res.sendFile(path.join(__dirname + "/index.html"));
});
app.get("/index.js", function (req, res) {
res.sendFile(path.join(__dirname + "/index.js"));
});
// Starts the server
app.listen(openPort, () => {
console.log(`Server started at http://localhost:${openPort}`);
});
