Overview For this assignment you need to make a database backed website that fea
ID: 3676669 • Letter: O
Question
Overview
For this assignment you need to make a database backed website that features Ajax interaction.
At the end of this description you will find code which will create a handler you can visit to set up your database table. It contains all the fields needed to make a simple workout tracker.
name - the name of the exercise
reps - the number of times the exercise was performed
weight - the weight of the weights used
date - the date the exercise was performed
lbs - a boolean indicating if the measurement is in lbs or kg. 1 indicates lbs, 0 indicates kgs.
Requirements
You need to create a single page application with the following functionality:
Visiting the page will show a table displaying all completed exercises. The header should list all the columns (id should not be displayed in the header or in the table itself. Use hidden inputs to keep track of the id).
At the top of the page there should be a form that lets you enter in all the data needed to make a new entry in the table with a button to submit it. Hitting that button should add the row to the table if it was successfully added to the database. If it was not successfully added (probably because name was left blank and it is required) it should not add it to the table.
Each row should have two buttons. One to delete the row and one to edit the row. Hitting the delete button should immediately remove the row from the table and from the database.
Hitting the edit button should make it possible to edit the data. For this function it is OK to go to another page which will allow you to edit that specific exercise, save it and then take you back to the main page. The form to edit the exercise should be pre-populated with the existing data from that row (in other words if I go to the edit page, and then hit save, nothing should change because all the old values were in the edit form).
All interactions, other than updating an exercise, should happen via Ajax. This means that at no time should the page refresh. Instead Ajax calls should be used to GET or POST to the server and it should use the data the server provides to update the page.
(Links to an external site.)
Here is an example of what I mean:
http://jsfiddle.net/GRgMb/ (Links to an external site.)
If you hit delete on one of those rows it gets deleted without the page refreshing. Your page should do this and it should update the database at the same time to reflect the deleted data. It should essentially happen in reverse when you add a row. You hit add and the table is populated with a new row.
Helpful Suggestions
Returning Data From The Database
Because the interactions should be handled via Ajax you often only want the database to send back an updated version of the table. Not a whole new page. If you go back to the very first example with Express.js in week 7 you will see an example where we return plain text rather than HTML in a fancy Handlebars template. You can use this same technique to return a simple JSON string (which conveniently is what is shown being displayed in the MySQL demos). Just send that back to the browser in response to an Ajax request and build a table using it rather than generating the HTML on the server.
You could even do this when you make the page initially. Just have JavaScript make an Ajax request when the page is loaded to get the JSON representing the table. You never even need to build a table on the server that way.
Handling the Update and Delete
Hidden fields will be key to deleting and updating data. Every row should have a form (or two froms) holding the update and delete buttons. That form should also have an input of type="hidden" which holds the id of the row so you can easily pass that information to the server to delete or update the row.
Explanation / Answer
dbcon.js
var mysql = require('mysql');
var pool = mysql.createPool({
host : 'localhost',
user : 'student',
password: 'default',
database: 'student'
});
module.exports.pool = pool;
index.js
var express = require('express');
var app = express();
var handlebars = require('express-handlebars').create({defaultLayout:'main'});
var bodyParser = require('body-parser');
var mysql = require('./dbcon.js');
app.use(bodyParser.urlencoded({ extended: false }));
app.engine('handlebars', handlebars.engine);
app.set('view engine', 'handlebars');
app.set('port', 3000);
app.get('/reset-table',function(req,res,next){
var context = {};
mysql.pool.query("DROP TABLE IF EXISTS workouts", function(err){ //replace your connection pool with the your variable containing the connection pool
var createString = "CREATE TABLE workouts("+
"id INT PRIMARY KEY AUTO_INCREMENT,"+
"name VARCHAR(255) NOT NULL,"+
"reps INT,"+
"weight INT,"+
"date DATE,"+
"lbs BOOLEAN)";
mysql.pool.query(createString, function(err){
context.results = "Table reset";
res.render('exercise',context);
})
});
});
app.get('/',function(req,res,next){
var context = {};
mysql.pool.query('SELECT * FROM workouts', function(err, rows, fields){
if(err){
next(err);
return;
}
context.exercise = rows;
res.render('exercise', context);
});
});
app.post('/',function(req,res){
var context = {};
if(req.body['Add Entry']){
mysql.pool.query("INSERT INTO workouts (`name`,`reps`,`weight`,`date`,`lbs`) VALUES (?,?,?,?,?)", [req.body.name,req.body.reps,req.body.weight,req.body.date,req.body.lbs], function(err, result){
if(err){
next(err);
return;
}
});
}
if(req.body['remove']){
mysql.pool.query("DELETE FROM workouts WHERE id = ?", [req.body.id], function(err, result){
if(err){
next(err);
return;
}
});
}
mysql.pool.query('SELECT * FROM workouts', function(err, rows, fields){
if(err){
next(err);
return;
}
context.exercise = rows;
res.render('exercise',context);
});
});
app.get('/update',function(req,res,next){
var context = {};
res.render('update',context);
});
app.post('/update',function(req,res,next){
var context = {};
mysql.pool.query("SELECT * FROM workouts WHERE id=?", [req.query.id], function(err, result){
if(err){
next(err);
return;
}
var curVals = result[0];
console.log(curVals);
mysql.pool.query("UPDATE workouts SET name=?, reps=?, weight=?, date=?, lbs=? WHERE id=? ",
[req.body.name || curVals.name, req.body.reps || curVals.reps, req.body.weight || curVals.weight, req.body.date || curVals.date, req.body.lbs || curVals.lbs, req.query.id],
function(err, result){
if(err){
next(err);
return;
}
context.results = "Updated " + result.changedRows + " rows.";
res.render('update',context);
});
});
});
app.use(function(req,res){
res.status(404);
res.render('404');
});
app.use(function(err, req, res, next){
console.error(err.stack);
res.type('plain/text');
res.status(500);
res.render('500');
});
app.listen(app.get('port'), function(){
console.log('Express started on http://localhost:' + app.get('port') + '; press Ctrl-C to terminate.');
});
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.