I have a little jTable that shows a list of records pulled from my local database using node.js with express-generator and mysql. I can show, create, edit and delete records in/from my database just fine.
My problem is that when I add a new record using my jTable, it creates an empty row in my jTable and doesn't populate it with the newly added data unless I refresh the page. This is weird because it successfully updates the row in the jTable when I edit and delete it using the jTable as well.
Can anyone see why this is happening?
(I excluded the edit and delete code since those work properly)
tbl_bar structure
rowID = INT, PK, AI
rowName = VARCHAR, UQ
rowOrder = INT (for future row organization)
index.js
var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password'
});
// Select which database to use
connection.query('USE db_foo;');
/*********************/
/***** SHOW ROWS *****/
/*********************/
// Retrieves all rows and sends them to the jTable for display
router.showRows = function (request, response) {
// Query table and return the ordered result/s
connection.query('SELECT * FROM tbl_bar ORDER BY rowOrder;', function (error, result) {
if (error) response.send({ Result: "ERROR", Message: "Error getting rows" });
else response.send({ Result: "OK", Records: result });
});
}
/**********************/
/***** CREATE ROW *****/
/**********************/
// Adds a new row to the database
router.createRow = function (request, response) {
var query = JSON.parse(JSON.stringify(request.body));
// Get the highest rowCount to determine order of newly added row
connection.query('SELECT rowOrder FROM tbl_bar ORDER BY rowOrder DESC LIMIT 1;', function (error, result) {
if (error) response.send({ Result: "ERROR", Message: "Error getting rows" });
else {
var data = {
rowName: query.rowName
};
// Increase highest order by 1 then add it to data JSON object, or use 0 if there are no rows
data['rowOrder'] = (result[0].rowOrder === null) ? 0 : result[0].rowOrder + 1;
// Query table and return the ordered result/s
connection.query('INSERT INTO tbl_bar SET ?', data, function (error, result) {
if (error) response.send({ Result: "ERROR", Message: "Error adding row" });
else response.send({ Result: "OK", Record: result });
});
}
});
}
/*************************/
/***** GET HOME PAGE *****/
/*************************/
router.get('/', function (request, response, next) {
response.render('index', { title: 'My jTable' });
});
// Event listeners
router.post('/showRows', router.showRows);
router.post('/createRow', router.createRow);
module.exports = router;
table.js
function populateTable() {
$('#container').jtable({
title: 'My jTable',
paging: false,
messages: {
addNewRecord: 'Add a New Row'
},
deleteConfirmation: function(data) {
data.deleteConfirmMessage = 'Delete Row: ' + data.record.rowName + '?';
},
actions: {
listAction: '/showRows',
createAction: '/createRow'
},
fields: {
rowID: {
title: 'ID',
key: true,
list: false,
create: false,
edit: false,
delete: false
},
rowName: {
title: 'Name',
key: false,
list: true,
create: true,
edit: true,
delete: true
},
rowOrder: {
title: 'Order',
key: false,
list: false,
create: false,
edit: true,
delete: true
}
}
});
}
$(document).ready(function () {
populateTable();
$('#container').jtable('load');
});
index.jade
doctype html
html
head
meta(charset='utf-8')
|
title My jTable
|
link(rel='stylesheet', type='text/css', href='javascripts/jquery-ui-1.11.4/jquery-ui.min.css')
|
link(rel='stylesheet', type='text/css', href='javascripts/jtable.2.4.0/themes/metro/blue/jtable.min.css')
|
script(type='text/javascript', src='javascripts/jquery-1.11.2.min.js')
|
script(type='text/javascript', src='javascripts/jquery-ui-1.11.4/jquery-ui.min.js')
|
script(type='text/javascript', src='javascripts/jtable.2.4.0/jquery.jtable.min.js')
|
script(type='text/javascript', src='javascripts/table.js')
|
body
#container
