Monday, October 24, 2016

MYSQL CRUD Operation using NodeJS

Step -1 Create Server.js file

var http = require('http'),
util = require('util'),
fs = require('fs'),
url = require('url'),
qs = require('querystring');
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'siddhu',
password : 'siddhu',
database : 'vscrum'
});
var server = http.createServer(function (req,res){
var url_parts = url.parse(req.url,true);
//console.log(url_parts);
var body = '';
if(req.method === 'POST'){
// res.end('post');
console.log('Request found with POST method');
req.on('data', function (data) {
body += data;
console.log('got data:'+data);
});
req.on('end', function () {
var POST = qs.parse(body);
// use POST
res.end("Sent data are id:"+POST.id+" name:"+POST.name);
});

} else {
console.log('Request found with GET method');
req.on('data',function(data){ res.end(' data event: '+data);});
if(url_parts.pathname == '/')
fs.readFile('./form.html',function(error,data){
console.log('Serving the page form.html');
res.end(data);
});
else if(url_parts.pathname == '/getData'){
console.log('Serving the Got Data.');
getData(res,url_parts);
}
//update
else if(url_parts.pathname == '/getDataForUpdate'){
console.log('Updating the Got Data.');
getDataForUpdate(res,url_parts);
}
//Delete
else if(url_parts.pathname == '/getDataforDelete'){
console.log('Deleting the Got Data.');
getDataforDelete(res,url_parts);
}
}
});
server.listen(1234);
console.log('Server listenning at localhost:1234');
function getData(res,url_parts){
console.log("Data submitted by the user id:"+url_parts.query.id+" and name:"+url_parts.query.name);
res.end("Data submitted by the user id:"+url_parts.query.id+" and name:"+url_parts.query.name);
connection.connect();
//insert into vscrum.nodejstable values ('1','sid')
//connection.query('SELECT * FROM vscrum.kpi', function(err, results)
var posts = {
id: url_parts.query.id,
name: url_parts.query.name
};
connection.query('INSERT INTO vscrum.nodejstable SET ?',posts, function(err, results)
{
if (err)
{
console.error(err);
}
else
{
console.log('First row of department table : ', results[0]);
}
});
connection.end();
}
//Update
function getDataForUpdate(res,url_parts){
console.log("Data submitted for Update by the user id:"+url_parts.query.id+" and name:"+url_parts.query.name);
res.end("Data submitted for Update by the user id:"+url_parts.query.id+" and name:"+url_parts.query.name);
connection.connect();
//update vscrum.nodejstable set name='siddharatha' where id = '1'
//connection.query('SELECT * FROM vscrum.kpi', function(err, results)
var posts = {
id: url_parts.query.id,
name: url_parts.query.name
};
//connection.query('UPDATE users SET Name = ? WHERE UserID = ?', [name, userId])
connection.query('UPDATE vscrum.nodejstable SET name=? where id=?',[url_parts.query.name,url_parts.query.id], function(err, results)
{
if (err)
{
console.error(err);
}
else
{
console.log('Row updated: ', results[0]);
}
});
connection.end();
}

//Delete
function getDataforDelete(res,url_parts){
console.log("Data submitted for Delete by the user id:"+url_parts.query.id+" and name:"+url_parts.query.name);
res.end("Data submitted for Delete by the user id:"+url_parts.query.id+" and name:"+url_parts.query.name);
connection.connect();
//delete from vscrum.nodejstable where id='2'
//connection.query('SELECT * FROM vscrum.kpi', function(err, results)
var posts = {
id: url_parts.query.id,
name: url_parts.query.name
};
connection.query('DELETE FROM vscrum.nodejstable where id=?', [url_parts.query.id], function(err, results)
{
if (err)
{
console.error(err);
}
else
{
console.log('Row deleted: ', results[0]);
}
});
connection.end();
}

Step -2 Create Form.html as given below
'-
'-
'-
'- Untitled Document
'-
'-
'-
'-

'-
'-
'-

'-
'-
'-

'-
'-
'-

'-

'-
'-


image5image4

No comments: