HTML - mysql db 연결해서 주소등록관리

2022-01-11

app.js

app.use('/address', require('./routes/address'));

//데이타베이스 접속
var db = require('./db');
db.connect(function(err){
  if(err){
    console.log('데이타베이스 접속오류....');
    process.exit(1);
  }
});

module.exports = app;

app.js 파일에 위부분을 추가해서 연결했다.

db.js

var mysql = require('mysql'); 
var conn;
exports.connect=function() {
    conn=mysql.createPool({
        connectionLimit:100, 
        host:'localhost', 
        user:'web1', 
        password:'pass', 
        database:'webdb1' 
    });
}

exports.get=function(){
    return conn;
};

mysql에 있는 값을 불러옴.

주소목록 index

var express = require('express');
var router = express.Router();

var db=require('../db')

/* 주소목록 데이타 출력 */

router.get('/', function(req, res, next) {
    var sql = "select * from tbl_address"
    db.get().query(sql, function(err, rows){
        res.send(rows); //데이타 출력 send
    });
});

/* 주소등록 */
router.get('/insert', function(req, res){
    res.render('insert', {"title":"주소등록"}); //화면출력 render
});

/* 주소DB저장 */
router.post('/insert', function(req,res){
    var name = req.body.name;
    var tel = req.body.tel;
    var address = req.body.address;
    var sql ="insert into tbl_address(name,tel,address) values(?,?,?)";
    db.get().query(sql, [name,tel,address], function(err,result){
        res.redirect('/');
    });
    console.log(`${name}-${tel}-${address}`);
});

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/common.css' />
    <script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
  </head>
  <body>
    <div id ="page">
      <div id ="top" style="width: 90%; height: 100px; ">

        <div id= "left">
          <div id ="main">
            <h3 id ="smallmenu" >메뉴</h3>
            </br>
            <h3 style="text-align: center;"><a href="/address/insert">주소등록</a></h3>
          </div>
        </div>
          <div id = "right">
        
          <h1><%= title %></h1>
        </div>
      </div>
   
    <div id = "center">
 
        <table id ="tbl"></table>
        <script id="temp" type="text/x-handlebars-template">
          <tr class = "title">
            <td width =100>번호</td>
            <td width =100>성명</td>
            <td width =200>전화번호</td>
            <td width =200>주소</td>
          </tr>
          
          <tr class = "row" onclick="location.href='/address/read?id='">
            <td></td>
            <td></td>
            <td></td>
            <td></td>
          </tr>
          
        </script>
     
   
      
</div>
<div id ="bottom">
  <h5>CopyRight 2022. 강깸 All Right Reserved.</h5>
  </div>
  </body>
  <script>
    getAddress();
    function getAddress(){
      $.ajax({
        type: "get",
        dataType : "json",
        url:"/address",
        success:function(data){
          var template = Handlebars.compile($("#temp").html());
          $("#tbl").html(template(data));
        }
      })
    }
  </script>
</html>

-> 주소목록 데이터를 출력하고 insert에 주소를 등록하고 DB에 저장한다.

주소 등록 insert

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel='stylesheet' href='/stylesheets/common.css' />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>


    <title><%=title%></title>
</head>
<body>
    <div id ="page">
        <div id = "top">
            <h1><%=title%></h1>
        </div>
        <div id ="center">
            <form name = "frm" method="post">
                <table>
                    <tr>
                        <td class="title" width=100>이름</td>
                        <td width=500><input type="text" name="name"></td>
                    </tr>
                    <tr>
                        <td class="title" width=100>전화번호</td>
                        <td width=500><input type="text" name="tel"></td>
                    </tr>
                    <tr>
                        <td class="title" width=100>주소</td>
                        <td width=500><input type="text" name="address" size=50></td>
                    </tr>
                </table>
                <div id="buttons">
                    <input type="submit" value="저장" class="button">
                    <input type="reset" value="취소" class="button">
                    <input type="button" value="목록"  onClick="location.href='/'" class="button">
                </div>
            </form>
        </div>
    </div>
    
</body>
<script>
    $(frm).on("submit", function(e){
        e.preventDefault();
        let name =$(frm.name).val();
        let tel = $(frm.tel).val();
        let address=$(frm.address).val();
        if(name==""){
            alert("이름을 입력하세요!");
            $(frm.name).focus();
        }else if(tel==""){
            alert("전화번호를 입력하세요!");
            $(frm.tel).focus();
        }else if(address==""){
            alert("주소를 입력하세요!");
            $(frm.address).focus();
        }else {
            if(!confirm(`${name}:${tel}:${address}을(를) 저장하실래요?`)) return;
            frm.submit();
        }

    });
</script>
</html>

-> 주소를 등록하는곳, 목록버튼이있어 메인목록으로 돌아갈수있다.

주소등록

주소 정보 read

address

/* 특정아이디 주소 읽기 */
router.get('/read',function(req, res){
    var id = req.query.id;
    var sql = "select * from tbl_address where id=?"
    db.get().query(sql, [id], function(err,rows){
        console.log(rows[0]);
        res.render('read', {"title":"주소정보", "vo":rows[0]});
    });
    
});

read

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel='stylesheet' href='/stylesheets/common.css' />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>


    <title><%=title%></title>
</head>
<body>
    <div id ="page">
        <div id = "top">
            <h1><%=title%></h1>
        </div>
        <div id ="center">
            <form name = "frm" method="post" action ="update">
                <table>
                    <tr>
                        <td class="title" width=100>번호</td>
                        <td width=500><input type="text" name="id" value ="<%=vo.id%>" readonly></td>
                    </tr>
                    <tr>
                        <td class="title" width=100>이름</td>
                        <td width=500><input type="text" name="name" value ="<%=vo.name%>"></td>
                    </tr>
                    <tr>
                        <td class="title" width=100>전화번호</td>
                        <td width=500><input type="text" name="tel" value="<%=vo.tel%>"></td>
                    </tr>
                    <tr>
                        <td class="title" width=100>주소</td>
                        <td width=500><input type="text" name="address" size=50 value="<%=vo.address%>"></td>
                    </tr>
                </table>
                <div id="buttons">
                    <input type="submit" value="수정" class="button" style ="background-color: rgb(218, 78, 78);">
                    <input type="reset" value="취소" class="button">
                    <input type="reset" value="삭제" class="button" style ="background-color: rgb(218, 78, 78);">
                    <input type="button" value="목록"  onClick="location.href='/'" class="button">
                </div>
            </form>
        </div>
    </div>
    
</body>
<script>
    $(frm).on("submit", function(e){
        e.preventDefault();
        let name =$(frm.name).val();
        let tel = $(frm.tel).val();
        let address=$(frm.address).val();
        if(name==""){
            alert("이름을 입력하세요!");
            $(frm.name).focus();
        }else if(tel==""){
            alert("전화번호를 입력하세요!");
            $(frm.tel).focus();
        }else if(address==""){
            alert("주소를 입력하세요!");
            $(frm.address).focus();
        }else {
            if(!confirm(`${name}:${tel}:${address}을(를) 수정하실래요?`)) return;
            frm.submit();
        }

    });
</script>
</html>

-> 주소목록에서 특정아이디를 클릭하게되면 해당되는 정보를 불러온다. 여기서 주소를 수정하거나 삭제하는것도 가능하다.

주소수정

address

/* 특정아이디 수정하기 */
router.post('/update',function(req,res){
    var id = req.body.id;
    var name = req.body.name;
    var tel = req.body.tel;
    var address = req.body.address;
    var sql ="update tbl_address set name=?,tel=?,address=? where id=?";
    // console.log(`${id}\n${name}/n${tel}\n${address}`);
    db.get().query(sql,[name,tel,address,id],function(err, result){
        res.redirect('/'); //목록으로 이동
    });
});

주소삭제

read 추가 (read 안의 삭제불러오기)

 $("#btnDelete").on("click",function(){
        var id =$(frm.id).val();
        if(!confirm(`${id}을(를) 삭제하실래요?`)) return;
        frm.action= "delete";
        frm.submit();
    });

address

//특정아이디를 DB에서 삭제
router.post('/delete',function(req,res){
    var id = req.body.id;
    var sql ="delete from tbl_address where id=?";
    db.get().query(sql,[id],function(err,result){
        res.redirect('/');
    });
});

주소정보

성적목록 score

db에서 테이블로 만들었던 성적목록을 주소정보 밑에 보여지게할것이다.

address

//특정학생의 성적목록
router.get('/score',function(req,res){
    var id = req.query.id;
    var sql='select id,date_format(sdate, "%Y-%m-%d") fdate,kor,eng,mat,(kor+eng+mat) tot,(kor+eng+mat)/3 avg from tbl_score where id=? order by sdate desc';    db.get().query(sql,[id],function(err,rows){
        res.send(rows);
    });

});

read

<hr/>
            <h1 id ="score"><%=vo.name%>의 성적</h1>
            <table id = "tbl">
            </table>
            <script id="temp" type="text/x-handlebars-template">
                <tr class = "title">
                    <td width=100>시험일</td>
                    <td width=80>국어</td>
                    <td width=80>영어</td>
                    <td width=80>수학</td>
                    <td width=80>총점</td>
                    <td width=80>평균</td>
                </tr>
                
                <tr class ="row">
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                </tr>

                

밑에 script

 var id = "<%=vo.id%>";

    getScore();
    function getScore(){
      $.ajax({
        type: "get",
        dataType : "json",
        url:"/address/score",
        data: {"id":id},
        success:function(data){
          var template = Handlebars.compile($("#temp").html());
          $("#tbl").html(template(data));
        }
      })
    }

누구의성적

Twitter Facebook LinkedIn

KangKkaem

HTML - mysql db 연결해서 주소등록관리

app.js

db.js

주소목록 index

주소 등록 insert

주소 정보 read

주소수정

주소삭제

성적목록 score

공유하기

댓글남기기

참고

CSS - Grid

CSS - Flex

[오류]Several ports (8005, 8080, 8009) required by Tomcat v7.0 Server at localhost are already in use.

git - github에 파일올리고 내려받기