Embedded Files
Create a MySQL database named ecommerce and design three tables: users with the fields user_id, username, email, password, and created_at; products with the fields product_id, name, description, price, and created_at; and reviews with the fields review_id, product_id, user_id, rating, review_text, and created_at. Define the necessary fields for each table and establish the relationships using foreign keys between the products and reviews tables and implement the following functionality:
Allow users to add new products with details such as name, description, and price.
Provide the ability for users to delete a product based on its review_id.
Enable users to retrieve product details using product_id.
------database creation------
var mysql = require('mysql');
// Create database connection
var con = mysql.createConnection({
host: "127.0.0.1",
user: "root",
password: "abcd"
});
// Connect to MySQL and create the database
con.connect(function(err) {
if (err) throw err;
console.log("Connected to MySQL!");
// Create the e-commerce database
con.query("CREATE DATABASE ecommerce", function(err, result) {
if (err && err.code !== 'ER_DB_CREATE_EXISTS') throw err;
if (result) console.log("Database created!");
else console.log("Database already exists!");
});
});
module.exports = con;
---Table creation---------
var mysql = require('mysql');
// Connect to the 'ecommerce' database
var con = mysql.createConnection({
host: "127.0.0.1",
user: "root",
password: "abcd",
database: "ecommerce"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected to the 'ecommerce' database!");
// Create the users table
const usersTable = `
CREATE TABLE IF NOT EXISTS users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
)`;
con.query(usersTable, function(err) {
if (err) throw err;
console.log("Users table created!");
});
// Create the products table
const productsTable = `
CREATE TABLE IF NOT EXISTS products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
user_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
)`;
con.query(productsTable, function(err) {
if (err) throw err;
console.log("Products table created!");
});
// Create the reviews table
const reviewsTable = `
CREATE TABLE IF NOT EXISTS reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
user_id INT,
review_text TEXT,
rating INT CHECK (rating BETWEEN 1 AND 5),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
)`;
con.query(reviewsTable, function(err) {
if (err) throw err;
console.log("Reviews table created!");
});
});
---ecommerce server---
const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');
const app = express();
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
// Database connection
const con = mysql.createConnection({
host: "127.0.0.1",
user: "root",
password: "abcd",
database: "ecommerce"
});
con.connect(function (err) {
if (err) throw err;
console.log("Connected to the e-commerce database!");
});
// Home Page with Forms
app.get('/', (req, res) => {
const html = `
<h1>E-commerce Application</h1>
<h2>Add a New Product</h2>
<form action="/add-product" method="POST">
Product Name: <input type="text" name="name" required><br>
Description: <textarea name="description" required></textarea><br>
Price: <input type="number" name="price" step="0.01" required><br>
User ID (who added the product): <input type="number" name="user_id" required><br>
<button type="submit">Add Product</button>
</form>
<h2>Retrieve Product Reviews</h2>
<form action="/product-reviews" method="GET">
Product ID: <input type="number" name="product_id" required><br>
<button type="submit">View Reviews</button>
</form>
<h2>Delete a Review</h2>
<form action="/delete-review" method="POST">
Review ID: <input type="number" name="review_id" required><br>
<button type="submit">Delete Review</button>
</form>
<h2>View All Products</h2>
<form action="/view-products" method="GET">
<button type="submit">View Products</button>
</form>
`;
res.send(html);
});
// 1. Add a new product to the database
app.post('/add-product', (req, res) => {
const { name, description, price, user_id } = req.body;
if (!name || !description || !price || !user_id) {
return res.status(400).send("All fields are required.");
}
const sql = `
INSERT INTO products (name, description, price, user_id)
VALUES (?, ?, ?, ?)
`;
con.query(sql, [name, description, price, user_id], function (err) {
if (err) {
console.error("Error adding product:", err);
return res.status(500).send("Error adding product.");
}
res.send(`<h1>Product added successfully.</h1><a href="/">Go Back</a>`);
});
});
// 2. Retrieve all reviews for a specific product
app.get('/product-reviews', (req, res) => {
const { product_id } = req.query;
const sql = `
SELECT r.review_id, r.review_text, r.rating, u.username
FROM reviews r
JOIN users u ON r.user_id = u.user_id
WHERE r.product_id = ?
`;
con.query(sql, [product_id], function (err, results) {
if (err) {
console.error("Error fetching reviews:", err);
return res.status(500).send("Error fetching reviews.");
}
let html = `<h1>Reviews for Product ID: ${product_id}</h1>`;
if (results.length > 0) {
html += `<table border="1"><tr><th>Review ID</th><th>Review Text</th><th>Rating</th><th>Username</th></tr>`;
results.forEach(review => {
html += `<tr><td>${review.review_id}</td><td>${review.review_text}</td><td>${review.rating}</td><td>${review.username}</td></tr>`;
});
html += `</table>`;
} else {
html += `<p>No reviews found for this product.</p>`;
}
html += `<a href="/">Go Back</a>`;
res.send(html);
});
});
// 3. Delete a review by ID
app.post('/delete-review', (req, res) => {
const { review_id } = req.body;
const sql = `DELETE FROM reviews WHERE review_id = ?`;
con.query(sql, [review_id], function (err, result) {
if (err) {
console.error("Error deleting review:", err);
return res.status(500).send("Error deleting review.");
}
if (result.affectedRows === 0) {
res.status(404).send(`<h1>Review not found.</h1><a href="/">Go Back</a>`);
} else {
res.send(`<h1>Review deleted successfully.</h1><a href="/">Go Back</a>`);
}
});
});
// 4. View all products in the database
app.get('/view-products', (req, res) => {
const sql = `SELECT * FROM products`;
con.query(sql, function (err, results) {
if (err) {
console.error("Error retrieving products:", err);
return res.status(500).send("Error retrieving products.");
}
let html = `<h1>All Products</h1>`;
if (results.length > 0) {
html += `<table border="1">
<tr>
<th>Product ID</th>
<th>Name</th>
<th>Description</th>
<th>Price</th>
<th>User ID</th>
</tr>`;
results.forEach(product => {
html += `<tr>
<td>${product.product_id}</td>
<td>${product.name}</td>
<td>${product.description}</td>
<td>${product.price}</td>
<td>${product.user_id}</td>
</tr>`;
});
html += `</table>`;
} else {
html += `<p>No products found.</p>`;
}
html += `<a href="/">Go Back</a>`;
res.send(html);
});
});
// Start the server
app.listen(9000, function () {
console.log("Server is running on http://localhost:9000");
});
Page updated
Google Sites
Report abuse