[Node.js] 자바스크립트 데이터베이스 연동 - 웹애플리케이션 6

이번에는 삭제버튼을 만들어볼 것이다.
edit버튼과 마찬가지로 특정 글이 선택되었을 때에만 출력하려고한다.

 

 

 

if topic
                li
                    a(href='/topic/'+topic.id+'/edit') edit
                li
                    a(href='/topic/' + topic.id + '/delete') delete

if문 조건 안에 있는 edit버튼 아래에 delete버튼을 추가했다.



이 상태에서 delete버튼을 누르면 Cannot GET /topic/id/delete 에러메시지가 날 것이다.
우리는 이에 해당하는 라우팅을 또 해줘야한다.

 

app.get('/topic/:id/delete', function (req, res) {
    
});

우선 프레임을 만들어준다.



add.pug 파일을 복사해서 delete.pug 파일로 한 개 더 생성한다.

doctype html
html
    head
        meta(charset='utf-8')
    body
        h1
            a(href='/topic') Server Title
        ul
            each topic in topics
                li
                    a(href='/topic/' + topic.id)= topic.title
        article
            h1= 'Delete? ' + topic.title
            a(href='/topic/' + topic.id + '/delete') yes
            a(href='/topic/' + topic.id) no

간단히 만들었다.

 

 

 

var sql = 'SELECT id,title FROM topic';
    var id = req.params.id;
    conn.query(sql, function (err, topics, fields) {
        var sql = 'SELECT * FROM topic WHERE id=?';
        conn.query(sql, [id], function (err, topic) {
            if (err) {
                console.log(err);
                res.status(500).send('Internal Server Error');
            } else {
                if (topic.length === 0) {
                    console.log('There is no record.');
                    res.status(500).send('Internal Server Error');
                } else {
                    res.render('delete', {topics: topics, topic: topic});
                }
            }
        });
    });

delete라우터 내부는 이와 같이 작성하였다.
sql 쿼리문을 작성하였고, 글의 글자수가 0일 때(글이 없을 때)를 필터링하였다.



삭제, 수정과 같이 특정페이지로 가지 않고 어떠한 작업을 하는 경우에는 GET 방식이 아니라 POST 방식을 사용해야한다.

doctype html
html
    head
        meta(charset='utf-8')
    body
        h1
            a(href='/topic') Server Title
        ul
            each topic in topics
                li
                    a(href='/topic/' + topic.id)= topic.title
        article
            h1= 'Delete? ' + topic.title
            form(action='/topic/' + topic.id + '/delete' method='post')
                p
                    input(type='submit' value='YES')
            a(href='/topic/' + topic.id) no

그래서 수정된 delete.pug 코드는 위와 같다.

여기에서 yes버튼을 누르게 되면 Cannot POST /topic/id/delete 에러가 뜰 것이다.
그럼 이를 해결하기 위해서는 당연히 라우팅을 해줘야한다.

 

 

 

app.post('/topic/:id/delete', function (req, res) {

});

delete의 POST 방식을 사용한 프레임을 만들어준다.

 

 

 

var id = req.params.id;
var sql = 'DELETE FROM topic WHERE id=?';
conn.query(sql, [id], function (err, result) {
    res.send(result);
})

res.send(result);를 통해 데이터가 정상적으로 동작하는지 테스트해본다.



정상적으로 작동했다면 아래 코드를 작성하여 마무리한다.

app.post('/topic/:id/delete', function (req, res) {
    var id = req.params.id;
    var sql = 'DELETE FROM topic WHERE id=?';
    conn.query(sql, [id], function (err, result) {
        res.redirect('/topic/');
    })
});

 

 

 

<add.pug 전체코드>

doctype html
html
    head
        meta(charset='utf-8')
    body
        h1
            a(href='/topic') Server Title
        ul
            each topic in topics
                li
                    a(href='/topic/' + topic.id)= topic.title
        article
            form(action='/topic/add' method='post')
                p
                    input(type='text', name='title' placeholder='title')
                p
                    textarea(name='description' placeholder='description')
                p
                    input(type='text', name='author' placeholder='author')
                p
                    input(type='submit')

 

<delete.pug 전체코드>

doctype html
html
    head
        meta(charset='utf-8')
    body
        h1
            a(href='/topic') Server Title
        ul
            each topic in topics
                li
                    a(href='/topic/' + topic.id)= topic.title
        article
            h1= 'Delete? ' + topic.title
            form(action='/topic/' + topic.id + '/delete' method='post')
                p
                    input(type='submit' value='YES')
            a(href='/topic/' + topic.id) no

 

<edit.pug 전체코드>

doctype html
html
    head
        meta(charset='utf-8')
    body
        h1
            a(href='/topic') Server Title
        ul
            each topic in topics
                li
                    a(href='/topic/' + topic.id)= topic.title
        article
            form(action='/topic/' + topic.id + '/edit' method='post')
                p
                    input(type='text', name='title' value=topic.title placeholder='title')
                p
                    textarea(name='description' placeholder='description')
                        =topic.description
                p
                    input(type='text', name='author' value=topic.author placeholder='author')
                p
                    input(type='submit')
        ul
            li
                a(href='/topic/add') add

 

<upload.pug 전체코드>

doctype html
html
    head
        meta(charset='utf-8')
    body
        form(action='upload' method='post' enctype="multipart/form-data")
            input(type='file' name='userfile')
            input(type='submit')

 

<view.pug 전체코드>

doctype html
html
    head
        meta(charset='utf-8')
    body
        h1
            a(href='/topic') Server Title
        ul
            each topic in topics
                li
                    a(href='/topic/' + topic.id)= topic.title
        article
            if topic
                h2= topic.title
                = topic.description
                div= 'by ' + topic.author
            else
                h2 Welcome
                | This is Web.
        ul
            li
                a(href='/topic/add') add
            if topic
                li
                    a(href='/topic/'+topic.id+'/edit') edit
                li
                    a(href='/topic/' + topic.id + '/delete') delete

 

<app_mysql.js 전체코드>

var express = require('express');
var bodyParser = require('body-parser');
var multer = require('multer');
var _storage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, 'uploads/')
    },
    filename: function (req, file, cb) {
        cb(null, file.originalname);
    }
})
var upload = multer({storage: _storage});
var fs = require('fs');
var mysql = require('mysql');
var conn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '1111',
    database: 'o2'
});
conn.connect();
var app = express();
app.use(bodyParser.urlencoded({extended: false}));
app.locals.pretty = true;
app.use('/user', express.static('uploads'));
app.set('views', './views_mysql');
app.set('view engine', 'pug');
app.get('/upload', function (req, res) {
    res.render('upload');
});
app.post('/upload', upload.single('userfile'), function (req, res) {
    console.log(req.file);
    res.send('Uploaded : ' + req.file.filename);
});
app.get('/topic/add', function (req, res) {
    var sql = 'SELECT id,title FROM topic';
    conn.query(sql, function (err, topics, fields) {
        if (err) {
            console.log(err);
            res.status(500).send('Internal Server Error');
        }
        res.render('add', {topics: topics});
    });
});
app.post('/topic/add', function (req, res) {
    var title = req.body.title;
    var description = req.body.description;
    var author = req.body.author;
    var sql = 'INSERT INTO topic (title, description, author) VALUES(?, ?, ?)';
    conn.query(sql, [title, description, author], function (err, result, fields) {
        if (err) {
            console.log(err);
            res.status(500).send('Internal Server Error');
        } else {
            res.redirect('/topic/' + result.insertId);
        }
    });
});
app.get(['/topic/:id/edit'], function (req, res) {
    var sql = 'SELECT id,title FROM topic';
    conn.query(sql, function (err, topics, fields) {
        var id = req.params.id;
        if (id) {
            var sql = 'SELECT * FROM topic WHERE id=?';
            conn.query(sql, [id], function (err, topic, fields) {
                if (err) {
                    console.log(err);
                    res.status(500).send('Internal Server Error');
                } else {
                    res.render('edit', {topics: topics, topic: topic[0]});
                }
            })
        } else {
            console.log('There is no id.');
            res.status(500).send('Internal Server Error');
        }
    });
});
app.post(['/topic/:id/edit'], function (req, res) {
    var title = req.body.title;
    var description = req.body.description;
    var author = req.body.author;
    var id = req.params.id;
    var sql = 'UPDATE topic SET title=?, description=?, author=? WHERE id=?';
    conn.query(sql, [title, description, author, id], function (err, result, fields) {
        if (err) {
            console.log(err);
            res.status(500).send('Internal Server Error');
        } else {
            res.redirect('/topic/' + id);
        }
    });
});
app.get('/topic/:id/delete', function (req, res) {
    var sql = 'SELECT id,title FROM topic';
    var id = req.params.id;
    conn.query(sql, function (err, topics, fields) {
        var sql = 'SELECT * FROM topic WHERE id=?';
        conn.query(sql, [id], function (err, topic) {
            if (err) {
                console.log(err);
                res.status(500).send('Internal Server Error');
            } else {
                if (topic.length === 0) {
                    console.log('There is no record.');
                    res.status(500).send('Internal Server Error');
                } else {
                    res.render('delete', {topics: topics, topic: topic[0]});
                }
            }
        });
    });
});
app.post('/topic/:id/delete', function (req, res) {
    var id = req.params.id;
    var sql = 'DELETE FROM topic WHERE id=?';
    conn.query(sql, [id], function (err, result) {
        res.redirect('/topic/');
    })
});
app.get(['/topic', '/topic/:id'], function (req, res) {
    var sql = 'SELECT id,title FROM topic';
    conn.query(sql, function (err, topics, fields) {
        var id = req.params.id;
        if (id) {
            var sql = 'SELECT * FROM topic WHERE id=?';
            conn.query(sql, [id], function (err, topic, fields) {
                if (err) {
                    console.log(err);
                    res.status(500).send('Internal Server Error');
                } else {
                    res.render('view', {topics: topics, topic: topic[0]});
                }
            })
        } else {
            res.render('view', {topics: topics});
        }
    });
});
app.listen(7777, function () {
    console.log('Connected, 7777 port!');
});