본문 바로가기
ICIA 수업일지

2021.08.05 수업일지{html, css, java script(jQuery),MySql,node.js}

by 주성씨 2021. 8. 7.

- nodejs를 통해 백엔드 작업을 하고 db와 연계하여 하는 작업을 할 수 있다.

- node.js의 npm(npm is a package manager for the JavaScript)툴을 이용해서 익스프레스(express)를 설치하면 백엔드를 작업할 수 있게 된다.

- express는 빠르게 웹 서버(웹 사이트)를 만들어주는 수단이다. 

- package.json> "dependencies" -> 라이브러리 모음

 

- 웹 작업을 해보자.

ex03>app.js

//2021.08.05
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

app.use('/', indexRouter);
app.use('/users', usersRouter);

 

ex03>routes>index.js

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

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: '익스프레스' }); //render ; 웹브라우저에 그려줘라
  // index.ejs 에서 title을 reder해라.
});

module.exports = router;

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
    <div id="container">
      <div id="header">
        <h1>Jewelry Shopping Mall</h1>
      </div>
      <div id="content"></div>
      <div id="footer">
        <h3>Copyright ICIA 이주성</h3>
      </div>
    </div>
  </body>
</html>

id는 유일성, class는 통합적

 

ex03>public>stylesheets>style.css

#container{
  width: 940px;
  margin: 0px auto;
  padding: 20px;
  border: 1px solid hotpink;
}
#header{
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
}

#content {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
  margin-top: 20px;
}

#footer {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
  margin-top: 20px;
}

#footer h3 {
  text-align: center;
}

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
    <div id="container">
      <div id="header">
        <img src="/images/back.jpg" width=900/>
      </div>
      <div id="content"></div>
      <div id="footer">
        <h3>Copyright ICIA 이주성</h3>
      </div>
    </div>
  </body>
</html>

 

초기 결과물

 

- MySQL에서 DB와 테이블을 만들어 웹에 출력할 데이터를 넣어보자.

#데이터 베이스 생성
create database jewelrydb;
#유저 생성
create user 'jewelry'@'localhost' identified by 'pass';
#유저에게 데이터베이스 권한 부여
grant all privileges on jewelrydb.* to 'jewelry'@'localhost';

 

create table product(
id int auto_increment primary key,
title nvarchar(200) not null,
price int default 0,
image nvarchar(100) not null
);

desc product;

insert into product(title, price, image)
values('[14k Gold] 스테디 원터치 링귀걸이',99900, '/images/img01.jpg');
insert into product(title, price, image)
values('[14kGold] 델리아 원터치 링귀걸이',69900, '/images/img02.jpg');
insert into product(title, price, image)
values('[925실버] 샤인모션 원터치 링귀걸이',20900, '/images/img03.jpg');
insert into product(title, price, image)
values('[925실버] 볼앤하트 원터치 링귀걸이',17900, '/images/img04.jpg');
insert into product(title, price, image)
values('[14kGold] 셔링 귀걸이',49900, '/images/img05.jpg');
insert into product(title, price, image)
values('[14kGold] 메르시 목걸이',15900, '/images/img06.jpg');
insert into product(title, price, image)
values('[925실버] 트윈 원터치 링귀걸이',20800, '/images/img07.jpg');
insert into product(title, price, image)
values('[14kGold] 한쪽 베이직 금볼 귀걸이',7900, '/images/img08.jpg');

select * from product;

 

select * from product
order by price desc
limit 0, 3;
# limit을 이용해 데이터를 제한해서 가지고 올 수 있다. 0 ~ 3 까지 원하는 갯수만큼 가지고 올 수 잇따.

 

# format을 이용해 단위를 1,000로 만들 수 있따.
select *, format(price, 0) fprice from product
order by price desc;

 

- back vsc

ex03>app.js

//2021.08.05
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var jewelry = require('./routes/jewelry');

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/jewelry', jewelry);

 

[new] ex03>routes>jewelry.js

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

/* GET users listing. */
router.get('/', function(req, res, next) {
  res.send('jewelry'); // send; 데이터를 출력하라.
});

module.exports = router;

+ 이제 DB연결하도록 하자.

 

[new] ex03>db.js

var mysql = require('mysql');
var conn;
exports.connect = function () {
    conn = mysql.createPool({
        connectionLimit: 100,
        host: 'localhost',
        user: 'jewelry', //*****/
        password: 'pass', //*****/
        database: 'jewelrydb' /*****/
    });
}
exports.get = function () {
    return conn;
};

 

터미널에서 빠져나와서 프로젝트 폴더경로에 npm install mysql을 한다.

 

mysql이 연결됨을 확인할 수 있다.

 

ex03>app.js

module.exports = app;

하단 위 코드 바로 위에 아래를

//2021.08.05
//데이타베이스 연결
var db = require('./db');
db.connect(function(err){
  if(err){
    console.log('DB error.......');
    process.exit(1);
  }else{
    console.log('DB Success......');
  }
});

입력

 

ex03>routes>jewelry.js

var express = require('express');
var router = express.Router();
var db = require('../db'); //*

/* GET users listing. */
router.get('/', function(req, res, next) {
    var sql='select * from product;';
    db.get().query(sql, [], function(err, rows){
        res.status(200).json(rows);
        // res.send(200).json(rows);
        
      });
});

module.exports = router;

 

위와 같이 로컬호스트로 연결되어야 한다.

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
    <script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
  </head>
  <body>
    <div id="container">
      <div id="header">
        <img src="/images/back.jpg" width=900/>
      </div>
      <div id="content">
        <h1>[상품 목록]</h1>
        <div id="product">

        </div>
      </div>
      <div id="footer">
        <h3>Copyright ICIA 이주성</h3>
      </div>
    </div>
  </body>
<script>
  // 함수는 불러오기전까지 실행 X
  getList();
  // function으로 만드는 이유는 재사용을 위해서
  function getList(){
  $.ajax({
    type: 'get',
    url: '/jewelry',
    dataType: 'json',
    success:function(data){
      alert(data.length);
    }
  });
  };
</script>
</html>

 

alert 명령어를 통해서 데이터 갯수를 위와 같이 출력할 수 있다.

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
    <script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
  </head>
  <body>
    <div id="container">
      <div id="header">
        <img src="/images/back.jpg" width=900/>
      </div>
      <div id="content">
        <h1>[상품 목록]</h1>
        <div id="product">

        </div>
      </div>
      <div id="footer">
        <h3>Copyright ICIA 이주성</h3>
      </div>
    </div>
  </body>
<script>
  // 함수는 불러오기전까지 실행 X
  getList();
  // function으로 만드는 이유는 재사용을 위해서
  function getList(){
  $.ajax({
    type: 'get',
    url: '/jewelry',
    dataType: 'json',
    success:function(data){
      var str='';
      $(data).each(function(){
        var id=this.id;
        var title=this.title;
        var image=this.image;
        var price=this.fprice;
        str+='<div class="box">';
          str+=`<img src="${image}" width=150 height=150/>`;
          str+='</div>';
      });
      $('#product').html(str);
    }
  });
  };
</script>
</html>

중간 결과물

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>

<head>
  <title><%= title %></title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
</head>

<body>
  <div id="container">
    <div id="header">
      <img src="/images/back.jpg" width=900 />
    </div>
    <div id="content">
      <h1>[상품 목록]</h1>
      <div id="product">

      </div>
    </div>
    <div id="footer">
      <h3>Copyright ICIA 이주성</h3>
    </div>
  </div>
</body>
<script>
  // 함수는 불러오기전까지 실행 X
  getList();
  // function으로 만드는 이유는 재사용을 위해서
  function getList() {
    $.ajax({
      type: 'get',
      url: '/jewelry',
      dataType: 'json',
      success: function (data) {
        var str = '';
        $(data).each(function () {
          var id = this.id;
          var title = this.title;
          var image = this.image;
          var price = this.fprice;
          str += '<div class="box">';
          str += `<img src="${image}" width=150 height=150/>`;
          str += `<div class="title">${id} : ${title}</div>`;
          str += `<div class="price" style="font-weight:bold; margin-left:15px">${price}원</div>`;
          str += '</div>';
        });
        $('#product').html(str);
      }
    });
  };
</script>

</html>

+ css

#container{
  width: 940px;
  margin: 0px auto;
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
}
#header{
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
}

#content {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
  margin-top: 20px;
}

#footer {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
  margin-top: 20px;
}

#footer h3 {
  text-align: center;
  color: rgb(54, 57, 69);
}

#product {
  width: 800px;
  overflow: hidden;
  margin: 0px auto;
}

.box {
  width: 170px;
  float: left;
  border: 1px solid rgb(224, 181, 137);
  margin-left: 10px;
  margin-bottom: 20px;
  box-shadow: 5px 5px 5px rgb(181, 90, 48);
  padding: 5px;
}

.box img {
  margin-left: 10px;
}

.box .title {
  white-space: nowrap;
  overflow: hidden;
  text-overflow: ellipsis;
  margin: 5px;
  font-size: 12px;
};

.box .price class가 왜 안들어가는지 확인해보자.

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>

<head>
  <title><%= title %></title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
</head>

<body>
  <div id="container">
    <div id="header">
      <img src="/images/back.jpg" width=900 />
    </div>
    <div id="content">
      <h1>[상품 목록]</h1>
      <div style="width:750px; margin:0px auto; padding: 10px; overflow: hidden;">
        <select id="key" style="height: 20px; float: right;">
          <option value="id">상품번호순</option>
          <option value="price desc">상품고가순</option>
          <option value="price">상품저가순</option>
          <option value="title">상품이름순</option>
        </select>
      </div>
      <div id="product"></div>
    </div>
    <div id="footer">
      <h3>Copyright ICIA 이주성</h3>
    </div>
  </div>
</body>
<script>
  // 함수는 불러오기전까지 실행 X
  getList();

  // key
  $('#key').on('change',function(){
    getList();
  });

  // function으로 만드는 이유는 재사용을 위해서
  function getList() {
    var key=$('#key').val();
    $.ajax({
      type: 'get',
      url: '/jewelry',
      dataType: 'json',
      data: {'key':key}, // var key=$('#key').val();를 'key'에 넣는다.
      success: function (data) {
        var str = '';
        $(data).each(function () {
          var id = this.id;
          var image = this.image;
          var title = this.title;
          var price = this.fprice;
          str += '<div class="box">';
          str += `<img src="${image}" width=150 height=150/>`;
          str += `<div class="title">${id} : ${title}</div>`;
          str += `<div class="price" style="font-weight:bold; margin-left:15px">${price}원</div>`;
          str += '</div>';
        });
        $('#product').html(str);
      }
    });
  };
</script>

</html>

 

ex03>routes>jewelry.js

var express = require('express');
var router = express.Router();
var db = require('../db'); //*

/* GET users listing. */
router.get('/', function(req, res, next) {
    var key = req.query.key;
    var sql=`select *, format(price,0) fprice from product order by ${key};`;
    db.get().query(sql, [], function(err, rows){
        res.status(200).json(rows);
        // res.send(200).json(rows);
      });
});

module.exports = router;

 

 

- make dummy data at mysql

insert into product(title,price,image)
select title, price, image from product;
# primary key가 auto이기 떄문에 가능하다.

select count(*) from product;

 

 

ex03>views>index.ejs

- 이미지 출력물 하단에 page 넘기는 버튼을 만들어 주도록 한다.

      </div>
      <div id="product"></div>
      <div id="pagination" style="width: 500px; margin: 0px auto; text-align: center; padding: 10px;">
        <button id="prev">&lt;</button>
        <span id="page">1</span>
        <button id="next">&gt;</button>
      </div>

 

- prev, next button의 function을 만든다.

<script>
  // page 전역변수
  var page = 1;

  // 함수는 불러오기전까지 실행 X
  getList();

  // key
  $('#key').on('change',function(){
    getList();
  });

  // prev 버튼을 눌렀을때
  $('#prev').on('click',function(){
    page--;
    $('#page').html(page);
    getList();
  });
  
  // next 버튼을 눌렀을때
  $('#next').on('click',function(){
    page++;
    $('#page').html(page);
    getList();
  });

  // function으로 만드는 이유는 재사용을 위해서
  function getList() {
    var key=$('#key').val();
    $.ajax({
      type: 'get',
      url: '/jewelry',
      dataType: 'json',
      data: {'key':key, 'page':page}, // var key=$('#key').val();를 'key'에 넣는다.
      success: function (data) {
        var str = '';
        $(data).each(function () {
          var id = this.id;
          var image = this.image;
          var title = this.title;
          var price = this.fprice;
          str += '<div class="box">';
          str += `<img src="${image}" width=150 height=150/>`;
          str += `<div class="title"><b>${id}</b> : ${title}</div>`;
          str += `<div class="price" style="font-weight:bold; margin-left:15px">${price}원</div>`;
          str += '</div>';
        });
        $('#product').html(str);
      }
    });
  };
</script>

 

ex03>routes>jewelry.js

- page, perPageNum값을 변수 선언하고 count

var express = require('express');
var router = express.Router();
var db = require('../db'); //*

/* GET users listing. */
router.get('/', function (req, res, next) {
    var key = req.query.key;
    var page = req.query.page; //**** */
    var perPageNum = 8; //**** */
    var start = (page - 1) * perPageNum //**** */

    var sql = `select *, format(price,0) fprice from product order by ${key} limit ?,?`; //**** */
    db.get().query(sql, [start, perPageNum], function (err, rows) {
        /* start 값이 sql문 ?로 들어간다. */
        res.send(rows);
        // res.send(200).json(rows);
    });
});

module.exports = router;

ㄴ sql 문 limit는 limt x,y에서 x번째에서 y갯수만큼 행을 출력하겠다는 것이다.

 

- mysql

create table notice(
	id int auto_increment primary key,
    title nvarchar(200) not null,
    content nvarchar(2000),
    wdate datetime default now()
);

select * from notice;

 

- 임의의 데이터값을 title, content에 넣는다.

insert into notice(title, content)
values('What is Lorem Ipsum?','Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.');
insert into notice(title, content)
values('Where does it come from?','Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old. Richard McClintock, a Latin professor at Hampden-Sydney College in Virginia, looked up one of the more obscure Latin words, consectetur, from a Lorem Ipsum passage, and going through the cites of the word in classical literature, discovered the undoubtable source. Lorem Ipsum comes from sections 1.10.32 and 1.10.33 of de Finibus Bonorum et MalorumThe Extremes of Good and Evil) by Cicero, written in 45 BC. This book is a treatise on the theory of ethics, very popular during the Renaissance. The first line of Lorem Ipsum, "Lorem ipsum dolor sit amet..", comes from a line in section 1.10.32.');
insert into notice(title, content)
values('Why do we use it?','It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using Content here, content here, making it look like readable English. Many desktop publishing packages and web page editors now use Lorem Ipsum as their default model text, and a search for lorem ipsum will uncover many web sites still in their infancy. Various versions have evolved over the years, sometimes by accident, sometimes on purpose (injected humour and the like)');
insert into notice(title, content)
values('Where can I get some?','There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration in some form, by injected humour, or randomised words which dont look even slightly believable. If you are going to use a passage of Lorem Ipsum, you need to be sure there isnt anything embarrassing hidden in the middle of text. All the Lorem Ipsum generators on the Internet tend to repeat predefined chunks as necessary, making this the first true generator on the Internet. It uses a dictionary of over 200 Latin words, combined with a handful of model sentence structures, to generate Lorem Ipsum which looks reasonable. The generated Lorem Ipsum is therefore always free from repetition, injected humour, or non-characteristic words etc.');

 

- 확인

select * from notice;

 

- 자가복사로 더미 데이터를 만들어준다.

insert into notice(title, content)
select title, content from notice;

 

- 확인

select count(*) cnt from notice;

ㄴ 256개의 임의의 더미데이터를 만들었다.

 

- 공지사항을 추가한다.

ex03>app.js

router을 만들어준다.

//2021.08.05
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var jewelry = require('./routes/jewelry');
var notice = require('./routes/notice'); /* */

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/jewelry', jewelry);
app.use('/notice', notice); /* */

 

[new] ex03>routes>notice.js

var express = require('express');
var router = express.Router();
var db = require('../db.js');

/* GET users listing. */
router.get('/', function(req, res, next) {
    var sql = 'select title, date_format(wdate,"%Y-%m-%d") fdate from notice order by id desc limit 0,10;';
    db.get().query(sql,[],function(err,rows){
        res.send(rows);
    });
});

module.exports = router;

ㄴ mysql date_format(wdate,"%Y-%m-%d")

https://devjhs.tistory.com/89

 

[mysql] DATE_FORMAT - 날짜 형식 설정

1. DATE_FORMAT - 역할 DATE_FORMAT(날짜 , 형식) : 날짜를 지정한 형식으로 출력 2. DATE_FORMAT - 구분기호  구분기호 역할 구분기호 역할  %Y 4자리 년도 %m 숫자 월 ( 두자리 )  %y 2자리 년도 %c 숫자..

devjhs.tistory.com

 

ex03>view>index.ejs

페이지 버튼과 footer 사이에 아래를 추가한다.

      <h1>공지사항</h1>
      <table id="tbl">
        <td>글번호</td>
        <td>글제목</td>
        <td>작성일</td>
      </table>

 

하단 스크립트에 위에 추가한 테이블에 공지사항을 출력할 수 있도록 한다.

  // 공지사항 출력
  $.ajax({
    type: 'get',
    url: '/notice',
    dataType: 'json',
    success: function (data) {
      var str = '';
      $(data).each(function () {
        var id = this.id;
        var title = this.title;
        var fdate = this.fdate;
        str += '<tr>';
        str += `<td>${id}</td><td class="title">${title}</td><td>${fdate}</td>`;
        str += '</tr>';
      });
      $('#tbl').append(str);
    }
  });

 

ex03>public>stylesheets>style.css

다음을 추가한다.

#tbl {
  border-collapse: collapse;
  margin: 0px auto;
  margin-bottom: 10px;
}

#tbl td {
  border-bottom: 1px solid rgb(210, 56, 108);
  padding: 10px;
}

#tbl .title {
  width: 500px;
}

 

공지사항을 위와 같이 만들었다.

 

ex03>public>stylesheets>style.css

#tbl .row {
  cursor: pointer;
}

#tbl .row:hover {
  background-color: rosybrown;
  color: seashell;
}

 

      <h1>공지사항</h1>
      <div id="tbl">
      </div>
    </div>

테이블에서 div로 바꿔준다.

 

        str += '<div class="row">';
        str += `<span class="id">${id}</span><span class="title">${title}</span><span class="fdate">${fdate}</span>`;
        str += `<div class="content">${content}</div>`;
        str += '</div>';

테이블에서 div span으로 바꿔준다.

https://coding-factory.tistory.com/189

 

[Html] Span 태그 사용법 & 예제

태그는 아주 다방면으로 활용이 가능한 유용한 태그입니다. 주로 와 태그와 함께 웹페이지의 일부분에 스타일을 적용시키기 위해 사용됩니다. span 태그안에 아무런 컨텐츠가 없다면 해당 부분

coding-factory.tistory.com

 

- 최종본

ex03>public>stylesheets>style.css

#container {
  width: 940px;
  margin: 0px auto;
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
}

#header {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
}

#content {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
  margin-top: 20px;
}

#footer {
  padding: 20px;
  border: 1px solid rgb(210, 56, 108);
  margin-top: 20px;
}

#footer h3 {
  text-align: center;
  color: rgb(54, 57, 69);
}

#product {
  width: 800px;
  overflow: hidden;
  margin: 0px auto;
}

.box {
  width: 170px;
  float: left;
  border: 1px solid rgb(224, 181, 137);
  margin-left: 15px;
  margin-bottom: 20px;
  box-shadow: 5px 5px 5px rgb(181, 90, 48);
  padding: 5px;
}

.box img {
  margin-left: 10px;
}

.box .title {
  white-space: nowrap;
  overflow: hidden;
  text-overflow: ellipsis;
  margin: 5px;
  font-size: 12px;
}

#tbl {
  border-collapse: collapse;
  margin: 0px auto;
  margin-bottom: 10px;
}

#tbl span {
  padding: 10px;
}

#tbl .title {
  display: inline-block;
  width: 500px;
}

#tbl .id {
  display: inline-block;
  width: 100px;
}

#tbl .content {
  padding: 10px;
  display: none;
}

#tbl .row {
  border-bottom: 1px solid rgb(210, 56, 108);
  cursor: pointer;
}

#tbl .row:hover {
  background-color: rosybrown;
  color: seashell;
}

 

ex03>routes>jewelry.js

var express = require('express');
var router = express.Router();
var db = require('../db'); //*

/* GET users listing. */
router.get('/', function (req, res, next) {
    var key = req.query.key;
    var page = req.query.page; //**** */
    var perPageNum = 12;
    var start = (page - 1) * perPageNum //**** */

    var sql = `select *, format(price,0) fprice from product order by ${key} limit ?,?`; //**** */
    db.get().query(sql, [start, perPageNum], function (err, rows) {
        /* start 값이 sql문 ?로 들어간다. */
        res.send(rows);
        // res.send(200).json(rows);
    });
});

/* GET users listing. */
router.get('/count', function(req,res){
    var sql = 'select count(*) cnt from product';
    db.get().query(sql,[],function(err,rows){
        res.send(rows[0]); // rows의 0번째 값이 count로 들어간다.
    });
});

module.exports = router;

 

ex03>routes>notice.js

var express = require('express');
var router = express.Router();
var db = require('../db.js');

/* GET users listing. */
router.get('/', function(req, res, next) {
    var sql = 'select *, date_format(wdate,"%Y-%m-%d %H:%i:%S") fdate from notice order by id desc limit 0,10;';
    db.get().query(sql,[],function(err,rows){
        res.send(rows);
    });
});

module.exports = router;

 

ex03>views>index.ejs

<!DOCTYPE html>
<html>

<head>
  <title><%= title %></title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
  <style>

  </style>
</head>

<body>
  <div id="container">
    <div id="header">
      <img src="/images/back.jpg" width=900 />
    </div>
    <div id="content">
      <h1>상품 목록</h1>
      <div style="width:750px; margin:0px auto; padding: 10px; overflow: hidden;">
        <select id="key" style="height: 20px; float: right;">
          <option value="id">상품번호순</option>
          <option value="price desc">상품고가순</option>
          <option value="price">상품저가순</option>
          <option value="title">상품이름순</option>
        </select>
        <span id="count" style="float: right;">전체상품갯수 : 0</span>
      </div>
      <div id="product"></div>
      <div id="pagination" style="width: 500px; margin: 0px auto; text-align: center; padding: 10px;">
        <button id="prev">&lt;</button>
        <span id="page">1</span>
        <button id="next">&gt;</button>
      </div>
      <h1>공지사항</h1>
      <div id="tbl">
      </div>
    </div>
    <div id="footer">
      <h3>Copyright ICIA 이주성</h3>
    </div>
  </div>
</body>
<script>
  // 전역변수
  var page = 1;
  var count = 0;
  var lastPage = 0;

  // 함수는 불러오기전까지 실행 X
  getList();

  //각 행을 클릭한 경우
  $('#tbl').on('click', '.row', function () {
    //모든 content를 숨긴다.
    $('#tbl .row .content').each(function () {
      $(this).hide();
    });
    //현재 선택한 content를 보여준다.
    var content = $(this).find('.content');
    content.show();
  });

  // 공지사항 출력
  $.ajax({
    type: 'get',
    url: '/notice',
    dataType: 'json',
    success: function (data) {
      var str = '';
      $(data).each(function () {
        var id = this.id;
        var title = this.title;
        var fdate = this.fdate;
        var content = this.content;
        str += '<div class="row">';
        str +=
          `<span class="id">${id}</span><span class="title">${title}</span><span class="fdate">${fdate}</span>`;
        str += `<div class="content">${content}</div>`;
        str += '</div>';
      });
      $('#tbl').append(str);
    }
  });

  // key
  $('#key').on('change', function () {
    getList();
  });

  // prev 버튼을 눌렀을때
  $('#prev').on('click', function () {
    page--;
    $('#page').html(page);
    getList();
  });

  // next 버튼을 눌렀을때
  $('#next').on('click', function () {
    page++;
    $('#page').html(page);
    getList();
  });

  // function으로 만드는 이유는 재사용을 위해서
  function getList() {
    var key = $('#key').val();
    $.ajax({
      type: 'get',
      url: '/jewelry',
      dataType: 'json',
      data: {
        'key': key,
        'page': page
      }, // var key=$('#key').val();를 'key'에 넣는다.
      success: function (data) {
        var str = '';
        $(data).each(function () {
          var id = this.id;
          var image = this.image;
          var title = this.title;
          var price = this.fprice;
          str += '<div class="box">';
          str += `<img src="${image}" width=150 height=150/>`;
          str += `<div class="title"><b>${id}</b> : ${title}</div>`;
          str += `<div class="price" style="font-weight:bold; margin-left:15px">${price}원</div>`;
          str += '</div>';
        });
        $('#product').html(str);

        //전체상품 갯수 출력
        $.ajax({
          type: 'get',
          url: '/jewelry/count',
          dataType: 'json',
          success: function (data) {
            count = data.cnt;
            lastPage = Math.ceil(count / 12);
            $('#count').html(`전체상품갯수:${count}개`);
            $('#page').html(`${page}/${lastPage}`);
          }
        });

        // 첫번째, 마지막 페이지에서 prev, next가 disabled 하게
        if (page == 1) $('#prev').attr('disabled', true);
        else $('#prev').attr('disabled', false);

        if (page == lastPage) $('#next').attr('disabled', true);
        else $('#next').attr('disabled', false);

      }
    });
  };
</script>

</html>

 

ex03>app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

//2021.08.05
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var jewelry = require('./routes/jewelry');
var notice = require('./routes/notice'); /* */

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/jewelry', jewelry);
app.use('/notice', notice); /* */


// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

//2021.08.05
//데이타베이스 연결
var db = require('./db');
db.connect(function(err){
  if(err){
    console.log('DB error.......');
    process.exit(1);
  }else{
    console.log('DB Success......');
  }
});

module.exports = app;

 

ex03>db.js

var mysql = require('mysql');
var conn;
exports.connect = function () {
    conn = mysql.createPool({
        connectionLimit: 100,
        host: 'localhost',
        user: 'jewelry', //*****/
        password: 'pass', //*****/
        database: 'jewelrydb' /*****/
    });
}
exports.get = function () {
    return conn;
};

 

저작자표시

'ICIA 수업일지' 카테고리의 다른 글

2021.08.09 수업일지(Mysql, node.js, html, css)  (0) 2021.08.09
2021.08.06 수업일지 {MySQL, html, css, java script(jQuery), node.js}  (0) 2021.08.07
2021.08.04 수업일지(node.js, MySQL, VSC)  (0) 2021.08.07
2021.08.03 수업일지(MySQL, jQuery, node.js)  (0) 2021.08.07
2021.08.02 수업일지(HTML, CSS, JAVA SCRIPT,MySQL)  (0) 2021.08.07

관련글

티스토리툴바