Adding data to mysql database with node.js

Question

I'm new to programming and trying to create a basic app with node.js.

I'm using node.js on visual studio with html instead of jade. I have a simple form with name, surname and gender. I have also managed to set up a database with mysql workbench.

I have the following query which adds the name, surname and gender successfully to the mysql database when I run the code.

var user = { "name": "Name", "surname": "Surname", "gender" : "M" };
    connection.query('INSERT INTO studentinfo SET ?', user, function (err, res) {
    if (err) throw err;
});

But I am stuck when the data has to be collected from the form when i click the submit button.

I guess its something to do with Ajax, I found some solutions with php, but I'm struggling with finding solutions with node.js and how and where to implement the code.

Here is the app.js file

/**
 * Module dependencies.
 */

var express = require('express');
var routes = require('./routes');
var http = require('http');
var path = require('path');
var mysql = require('mysql');

var app = express();

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'my_password',
    database: 'userlist',
});

connection.connect();

var user = { "name": "RandomName", "surname": "RandomSurnameName", "gender" : "M" };
connection.query('INSERT INTO studentinfo SET ?', user, function (err, res) {
    if (err) throw err;


});



//connection.query('SELECT * FROM studentinfo', function (err, rows) {
//    if (err) throw err;

//    console.log('Data received from Db:\n');
//    console.log(rows);
//});





// all environments
app.set('port', process.env.PORT || 3000);
app.set('views', path.join(__dirname, 'views'));
app.engine('html', require('ejs').renderFile);
app.set('view engine', 'html');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.json());
app.use(express.urlencoded());
app.use(express.methodOverride());
app.use(app.router);
app.use(require('stylus').middleware(path.join(__dirname, 'public')));
app.use(express.static(path.join(__dirname, 'public')));
// development only
if ('development' == app.get('env')) {
    app.use(express.errorHandler());
}

app.get('/', routes.index);
app.get('/about', routes.about);
app.get('/contact', routes.contact);
app.get('/newStudent', routes.newStudent);

http.createServer(app).listen(app.get('port'), function () {
    console.log('Express server listening on port ' + app.get('port'));
});


var createStudent = {

    name: String,
    surname: String,
    dob: Date,
    gender: String,

}


connection.query('insert into userlist set ?', createStudent, function (err, result) {

});

Answer 1

Ok, let's give it a shot.

On the HTML page, you need to submit the student details. A very very basic form looks like this:

<form action="/student" method="post">
  First name:<br>
  <input type="text" name="name"<br>
  Last name:<br>
  <input type="text" name="surname"><br>
  Date of birth:<br>
  <input type="text" name="dob"<br>
  Gender:<br>
  <input type="text" name="gender"><br><br>
  <input type="submit" value="Submit">
</form>

This is obviously oversimplified, but it will do the trick. So you have some boxes and a button that will POST this data to your server /student route.

Now we need to handle that route on the express server. You already have GET routes, so add this:

app.post('/', function (req, res) {
  // this is where you handle the POST request.
  var createStudent = {
    name: req.body.name,
    surname: req.body.surname,
    dob: req.body.dob,
    gender: req.body.gender
   }
   // now the createStudent is an object you can use in your database insert logic.
   connection.query('INSERT INTO studentinfo SET ?', createStudent, function (err, resp) {
     if (err) throw err;
     // if there are no errors send an OK message.
     res.send('Saved succesfully');
   });
 });