For this assignment you need to make a database backed website that features Ajax interaction. You have almost two weeks to work on this assignment. Please start as early as possible.

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

unit - indicating if the measurement is in lbs or kg

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 let 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. (Think back to the AJAX interactions assignment, where you updated your page's DOM to show the response you received from OpenWeatherMap, for example)

(Links to an external site.)Links to an external site.

Here is an example of what I mean:

http://jsfiddle.net/GRgMb/ (Links to an external site.)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.

Including JavaScript

Include static JavaScript files just like statics CSS files. See the content about organization in week 8. It discusses how to link a static CSS file. You can do the same thing to link static client side JS files.

Grading

Not implementing Ajax interactions will limit your maximum grade to an 80%. They add a fair bit of complexity so if you meet all the requirements but nothing works via Ajax then you can still get 80% credit for the assignment.

Properly implementing the viewing, editing, adding and deleting of data will be worth 25% each.

Deliverables

Add an URL to your site in the text box during submission. It is your responsibility to keep it up and running until you receive a grade. Submit a zip file containing all of your source code. Please include your source files and your package.json file (no need to include the node_modules folder and files)

app.get('/reset-table',function(req,res,next){ var context = {}; [your connection 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)"; [your connection pool].query(createString, function(err){ context.results = "Table reset"; res.render('home',context); }) }); }); 

Here is what I have so far - but I can't figure out how to write the handlebars file to render this -

var express = require('express'); var mysql = require('./dbcon.js'); var bodyParser = require('body-parser');

var app = express(); var handlebars = require('express-handlebars').create({defaultLayout:'main'});

app.engine('handlebars', handlebars.engine); app.use(bodyParser.urlencoded({extended:true})); app.use('/static', express.static('public')); app.set('view engine', 'handlebars'); app.set('mysql', mysql); app.set('port', 3000);

app.get('/reset-table', function(req, res, next){ var context = {}; mysql.pool.query("DROP TABLE IF EXISTS workouts", function(err){ 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); }) }); res.redirect('/exercise/'); });

app.get('/', function(req, res, next){ var context = {}; mysql.pool.query('SELECT id, name, reps, weight, date, lbs FROM workouts', function(err, rows, fields){ if(err){ next(err); return; } context.exercise = rows; res.render('exercise', context); }); });

app.post('/', function(req, res, next){ 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, results){ 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 id, name, weight, date, lbs FROM workouts', function(err, rows, fields){ if(err){ next(err); return; } context.exercise = rows; }); });

app.get('/update', function(req, res, next){ var context = {}; mysql.pool.query("SELECT * FROM workouts WHERE id=?", [req.body.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.changedRow + " 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.status(500); res.render('500'); });

app.listen(app.get('port'), function(){ console.log('App Listening'); });