본문 바로가기
프로그래밍 개발/NodeJs

Node.js and Mysql - My SQL join을 이용해서 구현하기

by Jinseok Kim 2021. 1. 11.
반응형

 

 

My SQL join을 이용해서 구현하기

 

 

 

 

mysql에 들어있는 위 두 테이블 데이터를 JOIN 기능을 Node.js에 적용하여 더 효율적으로 코드를 작성할 수 있다.

 

 

 

1. My SQL join을 이용해서 상세보기 구현

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;
              }
              //아래의 mysql 문의 JOIN을 적용하여 두 테이블을 합쳐 동시에 Node에서 사용할 수 있게 되었다.
              //이때 mysql문의 JOIN을 할때 두 테이블의 id가 겹치므로 WHERE topic.id처럼
              //정확한 테이블 위치를 지정해주어야한다.
          db.query(`SELECT * FROM topic LEFT JOIN author ON topic.author_id WHERE topic.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}
               <p>by ${topic[1].name}</P>`,
                                       
              `<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);
          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);

 

topic 테이블의 데이터와 author 테이블의 데이터를 한 곳에 적용시킬 수 있다는 것을 확인 할 수 있다.

 

 

 

 

 

 

 

 

2. My SQL join을 이용해서 글 생성 구현

 

 

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 LEFT JOIN author ON topic.author_id WHERE topic.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}
               <p>by ${topic[1].name}</P>`,
                                       
              `<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){
      
      //아래와 같이 author 테이블 데이터를 가져와 추가시켜주었다.
        db.query('SELECT * FROM author', function(error2, authors){
          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>
                ${template.authorSelect(authors)} //템플릿과 연결시켜주었다.
              </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);
          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);

 

 

 

 

 

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>
    `;
 
  },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>`;
      i = i + 1;
    }
    list = list+'</ul>';
    return list;
    
    //아래의 코드를 추가하여 main.js의 create부분에 템플릿을 통해 연결시켜주었다.
  },authorSelect:function(authors){
  
  //author 테이블 데이터에 대한 콤보 박스를 만들기 위하여 데이터를 돌려줘야 하는데
  //이때 while문을 통해 반복시켜주었다.
    var tag = '';
    var i = 0;
    while(i < authors.length){
      tag += `<option value="${authors[i].id}">${authors[i].name}</option>`;
      i++;
    }
    return `
      <select name="author">
        ${tag}
      </select>
    `
}
}

 

 

 

이제 create을 시도할때 by ~을 설정하여 함께 글 목록을 생성할 수 있게 되었다.

 

 

 

 

 

 

3. My SQL join을 이용해서 글 수정 구현

 

 

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'
});
db.connect();
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){
        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 {
        db.query(`SELECT * FROM topic`, function(error,topics){
         if(error){
           throw error;
         }
         db.query(`SELECT * FROM topic LEFT JOIN author ON topic.author_id=author.id WHERE topic.id=?`,[queryData.id], function(error2, topic){
           if(error2){
             throw error2;
           }
           console.log(topic);
          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}
            <p>by ${topic[0].name}</p>
            `,
            ` <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){
        db.query('SELECT * FROM author', function(error2, authors){
          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>
                ${template.authorSelect(authors)}
              </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);
          db.query(`
            INSERT INTO topic (title, description, created, author_id) 
              VALUES(?, ?, NOW(), ?)`,
            [post.title, post.description, post.author], 
            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;
    db.query('SELECT * FROM author', function(error2, authors){
          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>
              
              
              //update 버튼을 누르고 업데이트를 위한 폼창이 등장할 때 원래 값인 author 테이블 값을
              //그대로 보여주기 위하여 두번째 인자로 topic[0].author_id값을 템플렛으로 보내주었다.
               <p>
               ${template.authorSelect(authors, topic[0].author_id)}
               </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)*/
                                                  //author_id 값을 ?로 바꾸고 []안에 post.author을 추가해줌     
          db.query('UPDATE topic SET title=?, description=?, author_id=? WHERE id=?',[post.title, post.description, post.author, 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);
          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);

 

 

 

 

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>
    `;
 
  },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>`;
      i = i + 1;
    }
    list = list+'</ul>';
    return list;
    //두번째 인자로 선택한 목록 topic의 author_id값 받아옴
  },authorSelect:function(authors, author_id){
    var tag = '';
    var i = 0;
    while(i < authors.length){
    //while문 안에 selected 라는 변수를 추가하고 if문을 이용하여 만약 두번째 인자로 받아온
    //author_id값이 현재 설정한 콤보 박스의 값하고 똑같으면 selected을 <option> 태그 안에 넣어주어
    //html 특성으로 인하여 selected가 들어간 <option> 태그의 콤보 박스가 선택되므로 같은 콤보 박스 값을 선택할 수 있게 됨.
        var selected = '';
        if(authors[i].id === author_id){
            selected = ' selected';
        }
      tag += `<option value="${authors[i].id}"${selected}>${authors[i].name}</option>`;
      i++;
    }
    return `
      <select name="author">
        ${tag}
      </select>
    `
}
}

 

업데이트를 눌렀을때 원래 선택하고 있었던 콤보 박스 그대로 등장하며 다른 콤보 박스 선택을 하고 업데이트를 위하여 submit하자 그대로 방영된 것을 확인 할 수 있었다.

 

 

 

 

 

반응형

'프로그래밍 개발 > NodeJs' 카테고리의 다른 글

Node.js and Mysql - 저자 관련 기능 구현  (0) 2021.01.12
Node.js and Mysql - Node.js 정리정돈  (0) 2021.01.11
Node.js and Mysql - Node.js에 My SQL 구현하기  (0) 2021.01.11
Node.js - 출력정보에 대한 보안  (0) 2020.12.26
Node.js - 입력정보에 대한 보안  (0) 2020.12.26

관련글

댓글

티스토리툴바