CODEWITHSUNDEEP
javascriptmysqlnode.jsexpressejs
Florin Pop
Florin Pop

Reputation: 5135

MySql update after inserting nodejs

I made a simple app in nodejs with express framework and ejs template where I can insert into mysql database and see the results, but for some reasons when I insert and refresh the page, the new date is not showing, even if it puts it in the database.I need to close the server to see the new data.

My index.js route file looks like this:

var express = require('express');
var mysql = require('mysql');
var router = express.Router();

var users = [];
var age = [];

var connection = mysql.createConnection( {
    host: 'localhost',
    user: 'admin',
    password: 'pass',
    database: 'users'
});

console.log('\nconnecting to database');
connection.connect();

var query = connection.query('select * from users', function(err, result) {
    if(err) {
        console.error(err);
        return;
    } else {
        for(var i = 0; i<result.length; i++)
        {
            users.push(result[i]['user']);
            age.push(result[i]['age']);
        }
    }
});



/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { 
    title: 'Index page',
    users: users,
    age: age
  });
});


/* GET insert page. */
router.post('/insert', function(req, res, next) {
    var user = req.body.user;
    var age = req.body.age;

    var user_p = {
        user: user,
        age: age
    };

    console.log("post received: %s %s", user, age);

    var query = connection.query('insert into users set ?', user_p, function(err, result) {
        if(err) {
            console.error(err);
            return;
        } else {
            console.log(result);
        }
    });

    res.render('insert', { 
        title: 'Insert page',
        user: user_p
    });
});
// I've removed this connection.end() after I saw the error. But still not getting the new data out of the database when I go back to the index file
connection.end();

module.exports = router;

The index.ejs: 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head><% include partials/template/head.ejs %></head>
    <body>
        <h1><%= title %></h1>
        <p>Welcome to <%= title %></p>

        <ul>
            <% for(var i=0; i<users.length; i++) {%>
                <li>User: <%= users[i] %>, Age: <%= age[i] %></li>
            <% } %>
        </ul>


        <form action="/insert" method="post">
            <input type="text" name="user" placeholder="User"/>
            <input type="text" name="age" placeholder="Age"/>
            <input type="submit" value="send"/>
        </form>


    <% include partials/template/footer.ejs %>
    <% console.log('included footer'); %>
    </body>
</html>

and insert.ejs:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head><% include partials/template/head.ejs %></head>
    <body>
        <h1><%= title %></h1>
        <p>Welcome to <%= title %></p>

    <p>User: <%= user.user %></p>
    <p>Age: <%= user.age %></p>

    <% include partials/template/footer.ejs %>
    <% console.log('included footer'); %>
    </body>
</html>

How can I see "live" the changes in my database after I insert and refresh?

P.S. I just saw that I have the following error in my console:

{ [Error: Cannot enqueue Query after invoking quit.] code: 'PROTOCOL_ENQUEUE_AFTER_QUIT', fatal: false }

P.P.S. I've removed this connection.end() after I saw the error. But still not getting the new data out of the database when I go back to the index file

Upvotes: 0

Views: 2801

Answers (1)

Jerome WAGNER
Jerome WAGNER

Reputation: 22422

you never seem to update the array that are used when rendering '/'

var users = [];
var age = [];

are filled once using the 'select * from users' query but then they are never updated.

you could (in order to understand what is going on) add

console.log(result);
users.push(user_p.user);

in the ok branch of the 'insert into users set ?' request. This will show the new user.

Regarding the 'age', you cannot simply do the same thing because you have a 'var age' local variable shadowing your 'var age' module scope variable.

Upvotes: 1

Related Questions