Node.js with MySQL

In this post, I am going to put forward the approach to save data we sent by a HTML to Node.js server into MySQL database. 

Simple HTML form:

I created a simple HTML form that accepts username and password and on click of submit, sends the data to node.js server as post parameters.

Here is my HTML:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

<html> <head> <link rel="stylesheet" type="text/css" href="http://fonts.googleapis.com/css?family=Josefin+Slab"> <style> body { font-family: 'Josefin Slab'; font-size: 30px; color:white; background-color:#5882FA; } table { position:relative; bottom:-10px; } </style> </head> <body> <h1 style="text-align:center;">Form to save data to MySQL</h1> <p style="text-align:center;font-weight:bold;">Enter the text and click submit.</p> <form action="http://localhost:2000/requestServer" method="post" align='center'> <table align="center" style="font-size:30px;"> <tr> <td>User Name:</td> <td><input type="input" name="userName"/></td> </tr> <tr> <td>Password :</td> <td><input type="password" name="pwd"/></td> </tr> <tr><td colspan="2" align="center"><input type="submit" value="submit"/></td></tr> </table> </form> </body> </html>

Rendering:

My Node.js code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

var http=require("http"); var mysql = require("mysql"); var postDataObject; //connection to mysql var connection = mysql.createConnection({ host: "localhost", user: "root", password: "somePass", database: "mysql" }); connection.connect(); http.createServer(function(request, response) { request.on("data", function(postVars) { var dataFromPost = ""; if(request.url!="/favicon.ico") { var dataFromPostTemp = ""; dataFromPost+=postVars; var userName = dataFromPost.split("&")[0].split("=")[1]; var password1 = dataFromPost.split("&")[1].split("=")[1]; var date = new Date(); var formattedDate = date.getFullYear()+"-"+date.getMonth()+"-"+date.getDay(); postDataObject = {username: userName, password: password1, date: formattedDate}; connection.query("insert into users_nodejs set ?", postDataObject, function(err, result) { if(err) throw err; }); } }); request.on("end", function() { response.writeHead(200, {"Location":"e:/js/nodejs/page1.html"}); response.write("<p style='color:red;text-align:center;font-size:30px;'>A Node JS Server</p>"); response.end(); }); }).listen(2000); console.log("le succès de démarrage du serveur");

MySQL code:

To acquire node module mysql, one has to use:

1	var mysql = require("mysql");

Once we have got mysql module, we have to pass host, username, password and database name to createConnection of mysql instance as below.

1 2 3 4 5 6

var connection = mysql.createConnection({ host: "localhost", user: "root", password: "somePass", database: "mysql" });

I have passed the above to a variable 'connection'.

I used an object and passed the data I have to insert into table of mysql, in the object, data before column is column name and data after column is the data I got from post parameters.

For example from below:

1 2 3 4 5

postDataObject = {username: userName, password: password1, date: formattedDate}; connection.query("insert into users_nodejs set ?", postDataObject, function(err, result) { if(err) throw err; });

postDataObject has username:userName

here username is the column name in the table, userName is the variable into which I stored the post parameter, (refer to main Node.js program above).

And further there we have query in connection using which we will pass queries to MySQL.

General syntax of sql is 

Syntax specified for mysql module in node.js (from npmjs.com) is:

"Insert into table_name set ? ", obj

obj here is postDataObject that has columns and data.

Test from UI:

On click of submit, we have






In MySQL,







Don't hesitate to drop in any suggestions, happy learning 

A simple Node.js HTTP Server

In this post, I am going to show, how to create a simple Node.js HTTP server.

I took the example of creating the following:

1. A HTML that auto submits geolocation latitudes and longitudes to a Node.js HTTP Server.
2. A js file that creates HTTP Server using http module in Node.js.

The server:

In Node.js we can create a simple HTTP Server using http module.

we can import http module like this:

var http = require('http');

we can create server using the following:

we have to pass a simple callback to the 'createServer'.

http.createServer(function (request, response) {
response.writeHead(200, {'Content-Type':'text/html'});
response.write("<p style='color:red;font-size:30px'>This is a Node JS HTTP Server</p>");
response.end();
});

Using writeHead one can set status and content type.
Things we need to return to the client will be written in 'write'.
One has to definitely end the server using 'end', otherwise page loads and loads.

My complete server code:

1 2 3 4 5 6 7 8 9 10 11 12

var http=require('http'); var url = require('url'); http.createServer(function (request, response) { response.writeHead(200, {'Content-Type':'text/html'}); response.write("<p style='color:red;font-size:30px'>This is a Node JS HTTP Server</p>"); var latitude = url.parse(request.url).query.split("&")[0].split("=")[1]; var longitude = url.parse(request.url).query.split("&")[1].split("=")[1]; response.write("<p style='color:Red; font-size:30px;'>Position's latitude is: "+latitude+"<br/>Position's longitude is: "+longitude+"</p>"); console.log(latitude+" | "+longitude); response.write("<img src='https://maps.googleapis.com/maps/api/staticmap?center="+latitude+","+longitude+"&zoom=12&size=300x300&sensor=false'/>"); response.end(); }).listen(3000);

I used a HTML form that finds latitude and longitude using geolocation API in javascript and auto submits a form in 3 seconds.

The form submits itself to the server for a response.

My HTML code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

<html> <script> navigator.geolocation.getCurrentPosition(function(positionVar) { var coordinates = positionVar.coords; var br = document.createElement("br"); var inp1 = document.createElement("input"); inp1.value="Latitude"; inp1.disabled="disabled"; inp1.backgroundColor="red"; document.body.appendChild(inp1); var inp2 = document.createElement("input"); inp2.value="Longitude"; inp2.disabled="disabled"; inp2.backgroundColor="red"; document.body.appendChild(inp2); document.body.appendChild(br); var form1 = document.createElement("form"); form1.action='http://localhost:3000/latitudesAndLongitudes'; form1.method="get"; form1.id="f1"; document.body.appendChild(form1); var latitude = document.createElement("input"); latitude.type="text"; latitude.value=coordinates.latitude; latitude.name="latitude"; form1.appendChild(latitude); var longitude = document.createElement("input"); longitude.type="text"; longitude.value=coordinates.longitude; longitude.name="longitude"; form1.appendChild(longitude); form1.appendChild(br); var submit = document.createElement('input'); submit.type="submit"; submit.value="Click Here to navigate to the server"; form1.appendChild(submit); setTimeout(function() { document.forms["f1"].submit(); }, 3000); }, function (err) { throw err; }); </script> </html>

This also can be simply written as :) :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

<html> <head> <script> function function1() { navigator.geolocation.getCurrentPosition(function(positionVar) { var coordinates = positionVar.coords; document.getElementById("id4Lat").value=coordinates.latitude; document.getElementById("id4Lng").value=coordinates.longitude; var f1 = document.getElementById("f1"); setTimeout(function() { document.forms["f1"].submit(); }, 3000); }, function (err) { throw err; }); } </script> </head> <body onload='function1();'> <form action="http://localhost:3000/latitudesAndLongitudes" method="get" id="f1"> <input type="input" disabled="disabled" value="Latitude"/> <input type="input" disabled="disabled" value="Longitude"/> <br/> <input type="input" name="latitude" id="id4Lat"/> <input type="input" name="longitude" id="id4Lng"/> <br/> <input type="submit" value="Click Here else wait for 3 seconds"/> </form> </body> </html>

Results I have got:

I have masked the latitude and longitude on purpose, one can view them fully.

My HTML page:

Once this shows up user can click the submit button there below in 3 seconds, else it auto submits.

I assigned an id to the form and I used this code to auto submit the form to the server.

Action attribute is assigned with server's path and I used get method.

document.forms['f1'].submit();

I used the above code in a setTimeout method callback to buy some delay (3 seconds in this case).

Used general geolocation API to retrieve the location's latitude and longitude.

Server returns this:

Since I used get method to submit the form, you can see the query string in the address bar in above image showing us the parameters.

In node, we can get query string using url module like this:

var url = require('url');

url.parse(request.url).query;

Node logs:

Geolocation API by default is not supported by Node.js

One would have to install it using npm like this:

npm install geolocation

Once installed one can import geolocation using:

var geolocation = require('geolocation');

And general callback goes as normal javascript geolocation call.

Code goes like this with geolocation module installed on node.js

1 2 3 4 5 6 7

var geolocation = require('geolocation'); geolocation.getCurrentPosition(function(positionVar) { var coordinates = positionVar.coords; //lats and lngs likewise }, function(err) { throw err; });

Please don't hesitate to drop in any suggestions, happy learning.