vendredi 8 mai 2015

jTable not showing new row content after MySQL INSERT with node.js

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

Aucun commentaire:

Enregistrer un commentaire