dukDukz

21.04.25 게시판 페이지 write, read, update, delete 본문

웹 개발/DB | MYSQL

21.04.25 게시판 페이지 write, read, update, delete

헤일리_HJ 2021. 4. 25. 15:48

게시판 개요

210423_class.zip
3.33MB

 

 

코드

server.js

더보기 
const express = require('express');
const nunjucks = require('nunjucks');
const bodyParser = require('body-parser');
const mysql = require('mysql');

const app = express();

app.set('view engine', 'html');
nunjucks.configure('views', {
    express: app,
})

app.use(bodyParser.urlencoded({ extended: false }));


app.use(express.static('public'));


let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'homepage',
});
connection.connect();


app.get('/', (req, res) => {
    res.render('index.html');
});


app.get('/list', (req, res) => {
    // 1. db 에 있는 homepage -> board 라는 테이블을 select문 활용해 내용을 콘솔로그에 찍기
    // 2. 콘솔로그 찍은 내용을 list.html 데이터를 넘겨보자 <- nunjucks로 할 수 있음
    // 3. nunjucks 구문 사용해서 받아온 데이터를 조작하는걸 해본다.
    connection.query("SELECT idx, subject, board_name, content, date_format(today,'%Y-%m-%d') as today, hit FROM board", (error, results) => {
        if (error) {
            console.log(error);
        } else {
            //console.log(results);
            res.render('list.html', {
                list: results,
            });
        }
    });
});


app.get('/write', (req, res) => {
    res.render('board_write.html');
});

app.post('/write', (req, res) => {
    //console.log(req.body);
    let title = req.body.board_subject;
    let name = req.body.board_name;
    let content = req.body.board_content;

    connection.query(`insert into board (subject, board_name,content,hit) values('${title}','${name}','${content}',0)`,(error,results)=>{
        if(error){
            console.log(error);
        }else{
            res.redirect('/list');
        }
    })
});



app.get('/view', (req, res) => {
    // 조회수 hit 처리
    hit = parseInt(req.query.hit)+1 ;

    connection.query(`update board set hit=${hit} where idx=${req.query.idx}`, (error,results)=>{
        if (error) {
            console.log(error);
        } else {        
            //console.log(results);
        }
    });

    connection.query(`SELECT * FROM board WHERE idx=${req.query.idx};`, (error, results) => {
        if (error) {
            console.log(error);
        } else {        
            res.render('board_view.html', {
                list: results,
            });
        }
    });
});


app.get('/modify', (req, res) => {
    connection.query(`SELECT * FROM board WHERE idx=${req.query.idx};`, (error, results) => {
        if (error) {
            console.log(error);
        } else {        
            res.render('board_modify.html', {
                list: results,
            });
        }
    });
});
app.post('/modify', (req, res) => {
    let idx2 = req.query.idx;
    let title = req.body.board_subject;
    let name = req.body.board_name;
    let content = req.body.board_content;
    console.log(req.body);

    connection.query(`update board set subject='${title}', board_name='${name}', content='${content}' where idx=${idx2};`, (error,results)=>{
        if(error){
            console.log(error);
        }else{
            res.redirect('/list');
        }
    });
});


app.get('/delete',(req,res)=>{
    connection.query(`DELETE FROM board WHERE idx = '${req.query.idx}';`,(error,results)=>{
        if(error){
            console.log(error);
        }else{
            res.redirect('/list');
        }
    });
});


app.listen(3000, () => {
    console.log("server open");
});

list.html

더보기 
<!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">
    <title>Document</title>
    <link rel="stylesheet" href="./index.css">
</head>

<body>
    <h2>게시판 리스트 입니다</h2>
    <div class="board">
        <table>
            <!--한줄을 만들땐 tr을 많이 쓴다. 그 안에 td를 쓴다-->
            <tr>
                <td>번호</td>
                <td>제목</td>
                <td>작성자</td>
                <td>날짜</td>
                <td>조회수</td>
            </tr>

            {% for item in list%}
            <tr>
                <td> {{item.idx}} </td>
                <td> <a href="/view?idx={{item.idx}}&hit={{item.hit}}"> {{item.subject}} </a> </td>
                <td> {{item.board_name}} </td>
                <td> {{item.today}} </td>
                <td> {{item.hit}} </td>
            </tr>
            {% endfor %}

        </table>
        <a href="/write" class="write_btn">글쓰기</a>
    </div>

</body>

</html>

view.html

더보기 
<!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">
    <title>Document</title>
    <link rel="stylesheet" href="./index.css">  
</head>
<body>
    <h2>게시판 view 페이지 입니다</h2>
    {%for item in list%}
    <ul>
        <li>글 제목</li>
        <li>{{item.subject}}</li>
    </ul>
    <ul>
        <li>작성자</li>
        <li>{{item.board_name}}</li>
    </ul>
    <ul>
        <li>
            {{item.content}}
        </li>
    </ul>
    
        <a href="/list" class="write_btn">리스트가기</a>
        <a href="/modify?idx={{item.idx}}" class="write_btn">수정하기</a>
        <a href="/delete?idx={{item.idx}}" class="write_btn">삭제하기</a>
    {% endfor %}
</body>
</html>


<!--
    result값 받아와서 각각의 ul li에 뿌리기

-->

write.html

더보기 
<!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">
    <title>Document</title>
    <link rel="stylesheet" href="./index.css">
</head>

<body>
    <form action="/write" method="post">
        <h2>게시판 글쓰기 페이지 입니다</h2>
        <ul>
            <li>글 제목</li>
            <li><input type="text" name="board_subject"></li>
        </ul>
        <ul>
            <li>작성자</li>
            <li><input type="text" name="board_name"></li>
        </ul>
        <ul>
            <li>
                <textarea name="board_content" cols="30" rows="10"></textarea>
            </li>
        </ul>
        <input type="submit" value="글쓰기" class="write_btn">
        <a href="/list" class="write_btn">글목록</a>
    </form>
</body>

</html>

modify.html

더보기 
<!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">
    <title>Document</title>
    <link rel="stylesheet" href="./index.css">
</head>

<body>
    {% for item in list%}
    <form action="/modify?idx={{item.idx}}" method="post">
        <h2>게시판 글 수정 페이지 입니다</h2>
        
        <ul>
            <li>글 제목</li>
            <li><input type="text" name="board_subject" value="{{item.subject}}"></li>
        </ul>
        <ul>
            <li>작성자</li>
            <li><input type="text" name="board_name" value="{{item.board_name}}"></li>
        </ul>
        <ul>
            <li>
                <textarea name="board_content" cols="30" rows="10">{{item.content}}</textarea>
            </li>
        </ul>
        {% endfor %}
        <input type="submit" value="글 수정" class="write_btn">
        <a href="/list" class="write_btn">글목록</a>
    </form>
</body>

</html>

 

 

new 개념들

 

1. sql 사용

① createTable.sql 파일 만들고 구문 작성

CREATE DATABASE homepage;
use homepage;

CREATE TABLE board(
    idx INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(100) NOT NULL,
    board_name VARCHAR(50) NOT NULL,
    content TEXT,
    today DATETIME DEFAULT CURRENT_TIMESTAMP,
    hit INT(11)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

/*
maria db의 문법 맞지만
vscode가 sql문법이 아니라고 생각해서 나오는 오류

*/

② 창에 입력

 

 

2. today now()

now() 없이 쓰려면
insert into board(subject, board_name, content, hit) values('공지입니다','관리자','내용입니다',0);


아예 빼고 쓰면 됨,
맨 처음에  today DATETIME DEFAULT CURRENT_TIMESTAMP, 이렇게 default 값을 줘서 가능

 

 

 

3. today 보이는 형태

"SELECT idx, subject, board_name, content, date_format(today,'%Y-%m-%d') as today, hit FROM board_test"

 

 

 

4. 값 전달하기

<td><a href="/view?idx={{item.idx}}&hit={{item.hit}}">{{item.subject}}</a> </td>

이렇게 하면 post에서 쓸때도 req.query.idx 으로 써야함

 

.get   :   req.query.@
.post   :   req.body.@

 

 

5. 조회수 처리

 hit = parseInt(req.query.hit)+1 ;

    connection.query(`update board set hit=${hit} where idx=${req.query.idx}`, (error,results)=>{
        if (error) {
            console.log(error);
        } else {        
            //console.log(results);
        }
    });

'웹 개발 > DB | MYSQL' 카테고리의 다른 글

210506 Sequelize 상세 설명  (0) 2021.05.06
210504 MVC / Sequelize 시퀄라이즈 활용하기  (0) 2021.05.04
210503 테이블 여러개 JOIN ON  (0) 2021.05.03
21.04.28 DB Connection pool  (0) 2021.04.30
Marina DB의 기본 개념들  (0) 2021.04.21
'웹 개발/DB | MYSQL' Related Articles more