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) {

});

Can you tell us more about the code/setup? What does your page look like? What does your server look like? — paqash
– paqash, Commented Feb 23, 2017 at 22:52
I'm not with the system right now, but i'm just starting off with the default express template and have added this simple form to learn how to get the values into the database. Also, I didnt get your question about the server. could you please elaborate? — Su-J
– Su-J, Commented Feb 24, 2017 at 7:56
I'm not sure exactly where you need help. Is it submitting values from a form to the express server, creating a route on the server to handle the submit, calling a controller method from the route handler, or all of the above? — paqash
– paqash, Commented Feb 24, 2017 at 8:27
This terminology is too advanced for me. in simple words, I just i want to transfer the "Name" and "Surname" from the input fields into the database when i press a "submit" button. — Su-J
– Su-J, Commented Feb 24, 2017 at 8:31
You should probably go through a basic tutorial as there is a lot that needs to happen in order to do that. — paqash
– paqash, Commented Feb 24, 2017 at 8:44

Yash · Accepted Answer · 2019-08-31 11:26:28Z

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');
   });
 });

edited Aug 31, 2019 at 11:26

Yash

4176 silver badges22 bronze badges

answered Feb 24, 2017 at 17:21

paqash

2,3141 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Su-J Over a year ago

Nice! that works! thanks a lot.... i tried playing around with some of the statements and settings of the mysql table and finally it works. there are some things about the database i can figure out later, but the only thing which i'm wondering about this case is that the last line -->> re.send(); gives the comment inside on a new page and if i remove the statement, the page with the form stays, but the browser shows that some process is going on. whats happening there?

paqash Over a year ago

You can try something like res.status(200).send()

Su-J Over a year ago

That doesnt work, but i did find an answer to that... location reload onsubmit. Thanks for the help!

Collectives™ on Stack Overflow

Adding data to mysql database with node.js

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related