반응형
Node.js에Nodejs에 My SQL 구현하기
1. mysql npm 세팅
$ npm install mysql터미널에 위의 코드를 입력하여 mysql의 npm을 설치한다.
- mysql npm 홈페이지에서 복사해올 수 있는 세팅 코드를 js 파일 하나를 만들어 삽입한다.
- 주의할 점은 user, password, database의 mysql에서 설정했던 값들을 잘 입력해야한다.
이제 터미널에 $ npm install mysqljs/mysql을 입력하면 mysql의 데이터베이스 입력 데이터들이 쭉 나열되는 것을 확인 할 수 있다.
2. Node.js에 mysql 코드 설정
main.js
var http = require('http');
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql'); //추가
var db = mysql.createConnection({//추가
host:'localhost',
user:'root',
password:'061599',
database:'k0505s'
});
var app = http.createServer(function(request,response){
var _url = request.url;
var queryData = url.parse(_url, true).query;
var pathname = url.parse(_url, true).pathname;
if(pathname === '/'){
if(queryData.id === undefined){
/*fs.readdir('./data', function(error, filelist){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(filelist);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});*/
//위의 주석에 갇힌 코드 대신 아래와 같이 mysql을 연동시켜주었다.
//db.query안에 들어있는 첫번째 인자는 mysql문이 들어가고 두번째인자로는 콜백함수가
//들어가는데 이 콜백함수의 첫번째인자는 에라발생시 필요하고 두번째 인자는 정상적인
//결과를 호출되면 이 두번째 인자에 mysql의 결과가 담기게 되는 파라미터이다. 모두 일종의 약속이다.
db.query(`SELECT * FROM topic`, function(error, topics){
console.log(topics);
response.writeHead(200);
response.end('Success');
})
} else {
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var sanitizedTitle = sanitizeHtml(title);
var sanitizedDescription = sanitizeHtml(description, {
allowedTags:['h1']
});
var list = template.list(filelist);
var html = template.HTML(sanitizedTitle, list,
`<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
` <a href="/create">create</a>
<a href="/update?id=${sanitizedTitle}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${sanitizedTitle}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
});
});
}
} else if(pathname === '/create'){
fs.readdir('./data', function(error, filelist){
var title = 'WEB - create';
var list = template.list(filelist);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`, '');
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
} else if(pathname === '/update'){
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var list = template.list(filelist);
var html = template.HTML(title, list,
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${title}">
<p><input type="text" name="title" placeholder="title" value="${title}"></p>
<p>
<textarea name="description" placeholder="description">${description}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${title}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var filteredId = path.parse(id).base;
fs.unlink(`data/${filteredId}`, function(error){
response.writeHead(302, {Location: `/`});
response.end();
})
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
테스트 결과 Success가 등장하는 것으로 보아 정상적으로 작동하고 있다.
3. 본격적인 mysql 데이터를 홈페이지 목록에 직접 구현
main.js
var http = require('http');
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql');
var db = mysql.createConnection({
host:'localhost',
user:'root',
password:'061599',
database:'k0505s'
});
var app = http.createServer(function(request,response){
var _url = request.url;
var queryData = url.parse(_url, true).query;
var pathname = url.parse(_url, true).pathname;
if(pathname === '/'){
if(queryData.id === undefined){
/*fs.readdir('./data', function(error, filelist){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(filelist);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});*/
db.query(`SELECT * FROM topic`, function(error, topics){
//템플릿 모듈 파일에 topics 매개변수를 이용하여 연결해주었다.
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});
} else {
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var sanitizedTitle = sanitizeHtml(title);
var sanitizedDescription = sanitizeHtml(description, {
allowedTags:['h1']
});
var list = template.list(filelist);
var html = template.HTML(sanitizedTitle, list,
`<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
` <a href="/create">create</a>
<a href="/update?id=${sanitizedTitle}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${sanitizedTitle}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
});
});
}
} else if(pathname === '/create'){
fs.readdir('./data', function(error, filelist){
var title = 'WEB - create';
var list = template.list(filelist);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`, '');
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
} else if(pathname === '/update'){
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var list = template.list(filelist);
var html = template.HTML(title, list,
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${title}">
<p><input type="text" name="title" placeholder="title" value="${title}"></p>
<p>
<textarea name="description" placeholder="description">${description}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${title}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var filteredId = path.parse(id).base;
fs.unlink(`data/${filteredId}`, function(error){
response.writeHead(302, {Location: `/`});
response.end();
})
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
template.js
module.exports = {
HTML:function(title, list, body, control){
return `
<!doctype html>
<html>
<head>
<title>WEB1 - ${title}</title>
<meta charset="utf-8">
</head>
<body>
<h1><a href="/">WEB</a></h1>
${list}
${control}
${body}
</body>
</html>
`;
//매개변수인 topics을 통해 mysql의 데이터를 받아와 홈페이지에 보이도록 직접 구현하였다.
},list:function(topics){
var list = '<ul>';
var i = 0;
while(i < topics.length){
list = list + `<li><a href="/?id=${topics[i].id}">${topics[i].title}</a></li>`;
//topics 데이터 테이블의 정확한 값 이름을 .id .title 처럼 뒤에 적어주어야한다.
i = i + 1;
}
list = list+'</ul>';
return list;
}
}
실제 홈페이지에 mysql 데이터가 연결된 것을 목록의 이름 값을 보고 확인 할 수 있었다.
4. 목록 상세보기 구현하기
main.js
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql');
var db = mysql.createConnection({
host:'localhost',
user:'root',
password:'061599',
database:'k0505s'
});
var app = http.createServer(function(request,response){
var _url = request.url;
var queryData = url.parse(_url, true).query;
var pathname = url.parse(_url, true).pathname;
if(pathname === '/'){
if(queryData.id === undefined){
/*fs.readdir('./data', function(error, filelist){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(filelist);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});*/
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});
} else {
/*fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var sanitizedTitle = sanitizeHtml(title);
var sanitizedDescription = sanitizeHtml(description, {
allowedTags:['h1']
});
var list = template.list(filelist);
var html = template.HTML(sanitizedTitle, list,
`<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
` <a href="/create">create</a>
<a href="/update?id=${sanitizedTitle}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${sanitizedTitle}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
});
});
*/
//잘못된 입력으로 에러 발생시 throw로 error 뜨게 한다.
db.query(`SELECT * FROM topic`, function(error, topics){
if(error){
throw error;
}
//잘못된 입력으로 에러 발생시 throw로 error 뜨게 한다.
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
//queryData.id을 이용하여topic 데이터베이스의 id 값을 가져옴
if(error2){
throw error2;
}
var title = topic[0].title; //topic의 title 값 삽입
var description = topic[0].description; //topic의 description 값 삽입
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>
<a href="/update?id=${queryData.id}">update</a> //topic 데이터베이스의 id 값을 가져옴
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${queryData.id}"> //topic 데이터베이스의 id 값을 가져옴
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
})
});
}
} else if(pathname === '/create'){
fs.readdir('./data', function(error, filelist){
var title = 'WEB - create';
var list = template.list(filelist);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`, '');
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
} else if(pathname === '/update'){
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var list = template.list(filelist);
var html = template.HTML(title, list,
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${title}">
<p><input type="text" name="title" placeholder="title" value="${title}"></p>
<p>
<textarea name="description" placeholder="description">${description}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${title}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var filteredId = path.parse(id).base;
fs.unlink(`data/${filteredId}`, function(error){
response.writeHead(302, {Location: `/`});
response.end();
})
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
db.query(`SELECT * FROM topic`, function(error, topics){
if(error){
throw error;
}
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
if(error2){
throw error2;
}
var title = topic[0].title;
var description = topic[0].description;
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>
<a href="/update?id=${queryData.id}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${queryData.id}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
})
});
}
} else if(pathname === '/create'){
fs.readdir('./data', function(error, filelist){
var title = 'WEB - create';
var list = template.list(filelist);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`, '');
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
} else if(pathname === '/update'){
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var list = template.list(filelist);
var html = template.HTML(title, list,
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${title}">
<p><input type="text" name="title" placeholder="title" value="${title}"></p>
<p>
<textarea name="description" placeholder="description">${description}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${title}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var filteredId = path.parse(id).base;
fs.unlink(`data/${filteredId}`, function(error){
response.writeHead(302, {Location: `/`});
response.end();
})
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
목록을 클릭할때마다 각각의 목록의 데이터 값들을 불러온다.
5. 글 생성 기능 구현하기 Create
main.js
var http = require('http');
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql');
var db = mysql.createConnection({
host:'localhost',
user:'root',
password:'061599',
database:'k0505s'
});
var app = http.createServer(function(request,response){
var _url = request.url;
var queryData = url.parse(_url, true).query;
var pathname = url.parse(_url, true).pathname;
if(pathname === '/'){
if(queryData.id === undefined){
/*fs.readdir('./data', function(error, filelist){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(filelist);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});*/
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});
} else {
/*fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var sanitizedTitle = sanitizeHtml(title);
var sanitizedDescription = sanitizeHtml(description, {
allowedTags:['h1']
});
var list = template.list(filelist);
var html = template.HTML(sanitizedTitle, list,
`<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
` <a href="/create">create</a>
<a href="/update?id=${sanitizedTitle}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${sanitizedTitle}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
});
});
*/
db.query(`SELECT * FROM topic`, function(error, topics){
if(error){
throw error;
}
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
if(error2){
throw error2;
}
var title = topic[0].title;
var description = topic[0].description;
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>
<a href="/update?id=${queryData.id}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${queryData.id}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
})
});
}
} else if(pathname === '/create'){
//아래와 같이 create 호출 부분도 mysql과 연동하게끔 코드 작성
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Create';
var list = template.list(topics);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a>` );
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
/*var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})*/
//위의 주석에 갇힌 코드 대신 아래와 같이 mysql 연동을 위한 수정을 했다.
//mysql의 특정 데이터를 삽입시키는 명령 코드를 적어 주었다.
db.query(`INSERT INTO topic (title, description, created, author_id)
VALUES(?, ?, NOW(), ?)`,
[post.title, post.description, 1], function(error, result){
//위의 mysql의 코드 중 '?'에 들어갈 매치 값으로 []안에 각각 배열로 넣어주었다.
if(error){
throw error;
}
response.writeHead(302, {Location: `/?id=${result.insertId}`});
//사용자가 create을 이용하여 새로 생성하여 삽입한 id값을 알아내기 위하여
//result.insertId이라는 코드를 넣어주었다.
response.end();
}
)
});
} else if(pathname === '/update'){
fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var list = template.list(filelist);
var html = template.HTML(title, list,
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${title}">
<p><input type="text" name="title" placeholder="title" value="${title}"></p>
<p>
<textarea name="description" placeholder="description">${description}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${title}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})
});
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var filteredId = path.parse(id).base;
fs.unlink(`data/${filteredId}`, function(error){
response.writeHead(302, {Location: `/`});
response.end();
})
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
create 버튼을 누르고 글을 새로 생성하였다.
6. My SQL로 글수정 기능 구현
main.js
var http = require('http');
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql');
var db = mysql.createConnection({
host:'localhost',
user:'root',
password:'061599',
database:'k0505s'
});
var app = http.createServer(function(request,response){
var _url = request.url;
var queryData = url.parse(_url, true).query;
var pathname = url.parse(_url, true).pathname;
if(pathname === '/'){
if(queryData.id === undefined){
/*fs.readdir('./data', function(error, filelist){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(filelist);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});*/
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});
} else {
/*fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var sanitizedTitle = sanitizeHtml(title);
var sanitizedDescription = sanitizeHtml(description, {
allowedTags:['h1']
});
var list = template.list(filelist);
var html = template.HTML(sanitizedTitle, list,
`<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
` <a href="/create">create</a>
<a href="/update?id=${sanitizedTitle}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${sanitizedTitle}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
});
});
*/
db.query(`SELECT * FROM topic`, function(error, topics){
if(error){
throw error;
}
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
if(error2){
throw error2;
}
var title = topic[0].title;
var description = topic[0].description;
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>
<a href="/update?id=${queryData.id}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${queryData.id}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
})
});
}
} else if(pathname === '/create'){
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Create';
var list = template.list(topics);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a>` );
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
/*var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})*/
db.query(`INSERT INTO topic (title, description, created, author_id)
VALUES(?, ?, NOW(), ?)`,
[post.title, post.description, 1], function(error, result){
if(error){
throw error;
}
response.writeHead(302, {Location: `/?id=${result.insertId}`});
response.end();
}
)
});
} else if(pathname === '/update'){
db.query(`SELECT * FROM topic`,function(error, topics){ //추가
if(error){
throw error
}
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
if(error2){ //추가
throw error2
}
//fs.readdir('./data', function(error, filelist){
// var filteredId = path.parse(queryData.id).base;
//fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var list = template.list(topics); //topics로 수정
var html = template.HTML(topic[0], list, //topic[0]으로 수정
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${topic[0].id}"> //topic[0].id으로 수정
<p><input type="text" name="title" placeholder="title" value="${topic[0].title}"></p>
<p> //topic[0].title으로 수정
<textarea name="description" placeholder="description">${topic[0].description}</textarea>
</p> //topic[0].description으로 수정
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${topic[0].id}">update</a>`
); //topic[0].id으로 수정
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
/* var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err)*/
//글 업데이트를 위하여 mysql db에서 업데이트 기능을 구현하는 코드를 가져와 작성하였다.
db.query('UPDATE topic SET title=?, description=?, author_id=1 WHERE id=?'
,[post.title, post.description, post.id], function(error, result){
response.writeHead(302, {Location: `/?id=${post.id}`});
response.end();
})
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
var id = post.id;
var filteredId = path.parse(id).base;
fs.unlink(`data/${filteredId}`, function(error){
response.writeHead(302, {Location: `/`});
response.end();
})
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
생성 테스트를 했던 목록을 업데이트를 통해 수정하였더니 그대로 방영된 것을 확인 할 수 있었다.
7. My SQL로 글 삭제 기능 구현
main.js
var http = require('http');
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql');
var db = mysql.createConnection({
host:'localhost',
user:'root',
password:'061599',
database:'k0505s'
});
var app = http.createServer(function(request,response){
var _url = request.url;
var queryData = url.parse(_url, true).query;
var pathname = url.parse(_url, true).pathname;
if(pathname === '/'){
if(queryData.id === undefined){
/*fs.readdir('./data', function(error, filelist){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(filelist);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});*/
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Welcome';
var description = 'Hello, Node.js';
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});
} else {
/*fs.readdir('./data', function(error, filelist){
var filteredId = path.parse(queryData.id).base;
fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
var title = queryData.id;
var sanitizedTitle = sanitizeHtml(title);
var sanitizedDescription = sanitizeHtml(description, {
allowedTags:['h1']
});
var list = template.list(filelist);
var html = template.HTML(sanitizedTitle, list,
`<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
` <a href="/create">create</a>
<a href="/update?id=${sanitizedTitle}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${sanitizedTitle}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
});
});
*/
db.query(`SELECT * FROM topic`, function(error, topics){
if(error){
throw error;
}
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
if(error2){
throw error2;
}
var title = topic[0].title;
var description = topic[0].description;
var list = template.list(topics);
var html = template.HTML(title, list,
`<h2>${title}</h2>${description}`,
`<a href="/create">create</a>
<a href="/update?id=${queryData.id}">update</a>
<form action="delete_process" method="post">
<input type="hidden" name="id" value="${queryData.id}">
<input type="submit" value="delete">
</form>`
);
response.writeHead(200);
response.end(html);
})
});
}
} else if(pathname === '/create'){
db.query(`SELECT * FROM topic`, function(error, topics){
var title = 'Create';
var list = template.list(topics);
var html = template.HTML(title, list, `
<form action="/create_process" method="post">
<p><input type="text" name="title" placeholder="title"></p>
<p>
<textarea name="description" placeholder="description"></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a>` );
response.writeHead(200);
response.end(html);
});
} else if(pathname === '/create_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
/*var title = post.title;
var description = post.description;
fs.writeFile(`data/${title}`, description, 'utf8', function(err){
response.writeHead(302, {Location: `/?id=${title}`});
response.end();
})*/
db.query(`INSERT INTO topic (title, description, created, author_id)
VALUES(?, ?, NOW(), ?)`,
[post.title, post.description, 1], function(error, result){
if(error){
throw error;
}
response.writeHead(302, {Location: `/?id=${result.insertId}`});
response.end();
}
)
});
} else if(pathname === '/update'){
db.query('SELECT * FROM topic',function(error, topics){
if(error){
throw error
}
db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id], function(error2, topic){
if(error2){
throw error2
}
//fs.readdir('./data', function(error, filelist){
// var filteredId = path.parse(queryData.id).base;
//fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
//var title = queryData.id;
var list = template.list(topics);
var html = template.HTML(topic[0].title, list,
`
<form action="/update_process" method="post">
<input type="hidden" name="id" value="${topic[0].id}">
<p><input type="text" name="title" placeholder="title" value="${topic[0].title}"></p>
<p>
<textarea name="description" placeholder="description">${topic[0].description}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href="/create">create</a> <a href="/update?id=${topic[0].id}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
} else if(pathname === '/update_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
/* var id = post.id;
var title = post.title;
var description = post.description;
fs.rename(`data/${id}`, `data/${title}`, function(error){
fs.writeFile(`data/${title}`, description, 'utf8', function(err)*/
db.query('UPDATE topic SET title=?, description=?, author_id=1 WHERE id=?',[post.title, post.description, post.id], function(error, result){
response.writeHead(302, {Location: `/?id=${post.id}`});
response.end();
})
});
} else if(pathname === '/delete_process'){
var body = '';
request.on('data', function(data){
body = body + data;
});
request.on('end', function(){
var post = qs.parse(body);
//mysql의 문을 이용하여 지정한 데이터값을 삭제하는 코드를 작성함
db.query('DELETE FROM topic WHERE id = ?',[post.id], function(error, result){
if(error){
throw error;
}
response.writeHead(302, {Location: `/`});
response.end();
});
//var id = post.id;
//var filteredId = path.parse(id).base;
//fs.unlink(`data/${filteredId}`, function(error){
});
} else {
response.writeHead(404);
response.end('Not found');
}
});
app.listen(3000);
목록을 지정하고 삭제 버튼을 누르면 생성했던 목록들이 삭제되어지는 것을 확인 할 수 있었다.
반응형
'프로그래밍 개발 > NodeJs' 카테고리의 다른 글
| Node.js and Mysql - Node.js 정리정돈 (0) | 2021.01.11 |
|---|---|
| Node.js and Mysql - My SQL join을 이용해서 구현하기 (0) | 2021.01.11 |
| Node.js - 출력정보에 대한 보안 (0) | 2020.12.26 |
| Node.js - 입력정보에 대한 보안 (0) | 2020.12.26 |
| Node.js - 기본적인 CRUD APP 기능들 구현하기 (0) | 2020.12.19 |
댓글