Node.js自學筆記 (10/12)：完善Address Book

Web開發 Node.js
1

從上一個章節繼續出發：https://forum.stdb.org/t/topic/31055

加上導航列

先把NavBar加上Address Book的相關連結，修改/views/parts/navbar.ejs：

<nav class="navbar navbar-expand-lg navbar-light bg-light">
    <div class="container">
        <a class="navbar-brand" href="/">Navbar</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>

        <div class="collapse navbar-collapse" id="navbarSupportedContent">
            <ul class="navbar-nav mr-auto">
                <li class="nav-item active">
                    <a class="nav-link" href="/">Home <span class="sr-only">(current)</span></a>
                </li>
                <li class="nav-item dropdown">
                    <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button"
                        data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                        Dropdown
                    </a>
                    <div class="dropdown-menu" aria-labelledby="navbarDropdown">
                        <a class="dropdown-item" href="/try-qs?b=1&a[abc]=2">query string</a>
                        <a class="dropdown-item" href="/try-post-form">try-post-form</a>
                        <div class="dropdown-divider"></div>
                        <a class="dropdown-item" href="#">Something else here</a>
                    </div>
                </li>
                <li class="nav-item dropdown">
                    <a class="nav-link dropdown-toggle" href="/address-book" id="navbarDropdown" role="button"
                        data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                        Address Book
                    </a>
                    <div class="dropdown-menu" aria-labelledby="navbarDropdown">
                        <a class="dropdown-item" href="/address-book/list">List</a>
                        <a class="dropdown-item" href="/address-book/add">Add</a>
                        <div class="dropdown-divider"></div>
                        <a class="dropdown-item" href="#">Something else here</a>
                    </div>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="#">Link</a>
                </li>
            </ul>
        </div>
    </div>
</nav>

修改/src/address_book.js

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

router.get('/list', (req, res) => {
    db.query("SELECT * from address_book LIMIT 10")
        // db.query("SELECT COUNT(*) num from address_book")
        .then(([rows]) => {
            res.render('address-book/list', { rows })
            // res.json(result);
        })
});

module.exports = router;

製作分頁

/src/address_book.js

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

router.get('/list/:page?', (req, res) => {
    const perPage = 5;
    let page = parseInt(req.params.page) || 1;
    const output = {
        page: page,  // 目前頁數
        perPage: perPage,
        totalRows: 0,  // 總共資料筆數
        totalPages: 0,  // 總共頁數
        rows: []  // 資料內容

    }
    db.query("SELECT COUNT(*) num from address_book")
        .then(([r]) => {
            // res.json(r);
            output.totalRows = r[0].num;
            output.totalPages = Math.ceil(output.totalRows / perPage)
            if (page < 1) page = 1;
            if (output.totalPages === 0) page = 0;
            output.page = page;
            if (!output.page) {
                return [null];
            } else {
                const sql = `SELECT * FROM address_book LIMIT ${(page - 1) * perPage},${perPage}`
                return db.query(sql)
            }
        })
        .then(([r]) => {
            if (r) output.rows = r;
            res.json(output);
        })
        .catch(ex => console.log(ex))
    // db.query("SELECT * from address_book LIMIT 10")
    //     // db.query("SELECT COUNT(*) num from address_book")
    //     .then(([rows]) => {
    //         res.render('address-book/list', { rows })
    //         // res.json(result);
    //     })
});

module.exports = router;

實測：
http://localhost:3000/address-book/list/3

image
image1042×390 61.4 KB

http://localhost:3000/address-book/list/3

image
image1041×385 61.7 KB

http://localhost:3000/address-book/list/3

image
image1049×111 8.39 KB

再把 res.json(output); 改為 res.render('address-book/list', { rows })，就可以顯示表格。

使用 async, await 改寫

若把/src/address_book.js用async, await改寫，同樣的功能可以改寫如下：

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

router.get('/list/:page?', async (req, res) => {
    const perPage = 5;
    let page = parseInt(req.params.page) || 1;
    const output = {
        page: page,  // 目前頁數
        perPage: perPage,
        totalRows: 0,  // 總共資料筆數
        totalPages: 0,  // 總共頁數
        rows: []  // 資料內容

    }
    const [r1] = await db.query("SELECT COUNT(*) num from address_book")
    output.totalRows = r1[0].num;
    output.totalPages = Math.ceil(output.totalRows / perPage)
    if (page < 1) page = 1;
    if (output.totalPages === 0) page = 0;
    output.page = page;
    if (!output.page) {
        return res.json(output);
    }
    const sql = `SELECT * FROM address_book LIMIT ${(page - 1) * perPage},${perPage}`
    const [r2] = await db.query(sql);
    if (r2) output.rows = r2;
    res.render('address-book/list', output)
});

module.exports = router;

改成通用寫法

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

const getDataList = async (req) => {
    const perPage = 5;
    let page = parseInt(req.params.page) || 1;
    const output = {
        page: page,  // 目前頁數
        perPage: perPage,
        totalRows: 0,  // 總共資料筆數
        totalPages: 0,  // 總共頁數
        rows: []  // 資料內容

    }
    const [r1] = await db.query("SELECT COUNT(*) num from address_book")
    output.totalRows = r1[0].num;
    output.totalPages = Math.ceil(output.totalRows / perPage)
    if (page < 1) page = 1;
    if (output.totalPages === 0) page = 0;
    output.page = page;
    if (!output.page) {
        return output;
    }
    const sql = `SELECT * FROM address_book LIMIT ${(page - 1) * perPage},${perPage}`
    const [r2] = await db.query(sql);
    if (r2) output.rows = r2;
    return output;
};

router.get('/list/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.render('address-book/list', output)
});

router.get('/list/api/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.json(output)
});

module.exports = router;

只要在網址加上api，就切到另一個路由，把同一個output物件用json傳到螢幕上。

實測：

image
image1041×385 61.8 KB

同一份程式，可以按照不同需求，呼叫同一個程式，做不同的輸出。

在List頁面加上換頁按鈕

參考Bootatrap的資料：Pagination · Bootstrap v4.5

image

再加上日期調整：
address_book.js

const express = require('express');
const moment = require('moment-timezone');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

const getDataList = async (req) => {
    const perPage = 5;
    let page = parseInt(req.params.page) || 1;
    const output = {
        page: page,  // 目前頁數
        perPage: perPage,
        totalRows: 0,  // 總共資料筆數
        totalPages: 0,  // 總共頁數
        rows: []  // 資料內容

    }
    const [r1] = await db.query("SELECT COUNT(*) num from address_book")
    output.totalRows = r1[0].num;
    output.totalPages = Math.ceil(output.totalRows / perPage)
    if (page < 1) page = 1;
    if (output.totalPages === 0) page = 0;
    output.page = page;
    if (!output.page) {
        return output;
    }
    const sql = `SELECT * FROM address_book LIMIT ${(page - 1) * perPage},${perPage}`
    const [r2] = await db.query(sql);
    if (r2) output.rows = r2;
    for (let i of r2){
        i.birthday = moment(i.birthday).format('YYYY/MM/DD hh:mm:ss')
    }
    return output;
};

router.get('/list/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.render('address-book/list', output)
});

router.get('/list/api/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.json(output)
});

module.exports = router;

實測結果：

image
image917×802 44.1 KB

新增資料

在addres_book.js新增以下路由：

const upload = require(__dirname+'/upload-module');
// 這裡不會做到檔案上傳，使用upload-module.js的multer來處理表單

router.get('/add', (req, res) => {
    res.render('address-book/add', output)
});

router.post('/add', upload.none(), (req, res) => {
    res.json({ok:"ok"})
    // res.render('address-book/add', output)
});

複製list.ejs到add.ejs，參考Bootstrap的元件：Cards · Bootstrap v4.5
Forms · Bootstrap v4.5

以及第三方的Bootstrap元件： Tempus Dominus datepicker

add.ejs修改如下：

<%- include ('../parts/html-head') %>
<%- include ('../parts/navbar') %>
<div class="container">
    <div class="row">
        <div class="col-lg-6">
            <div class="card">
                <!-- <img src="..." class="card-img-top" alt="..."> -->
                <div class="card-body">
                    <h5 class="card-title">新增資料</h5>
                    <form name="form1" onsubmit="return checkForm()">
                        <div class="form-group">
                            <label for="name">name</label>
                            <input type="text" class="form-control" id="name" name="name" required>
                        </div>
                        <div class="form-group">
                            <label for="email">Email address</label>
                            <input type="email" class="form-control" id="email" name="email" required>
                        </div>
                        <div class="form-group">
                            <label for="mobile">Mobile</label>
                            <input type="text" class="form-control" id="mobile" name="mobile"
                                pattern="09\d{2}-?\d{3}-?\d{3}">
                        </div>
                        <div class="birthday">
                            <label for="exampleInputEmail1">Birthday</label>
                            <input type="text" class="form-control" id="birthday" name="birthday">
                        </div>
                        <div class="address">
                            <label for="exampleInputEmail1">Address</label>
                            <input type="text" class="form-control" id="address" name="address" required>
                        </div>

                        <button type="submit" class="btn btn-primary">Submit</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
</div>
<%- include('../parts/scripts') %>
<script>
    function checkForm() {
        // 檢查欄位的格式
        const fd = new FormData(document.form1);
        fetch('/address-book/add',{
            method: 'POST',
            body: fd
            // 傳送的方式為mutiple form data, 不是url encoded，所以在address_book.js才要用upload.none()
        })
        .then(r=>r.json())
        .then(obj=>{
            console.log(obj);
        })
        return false;
    }
</script>
<%- include('../parts/html-foot') %>

address_book.js修改如下：

const express = require('express');
const moment = require('moment-timezone');
const upload = require(__dirname+'/upload-module');
// 這裡不會做到檔案上傳，使用upload-module.js的multer來處理表單
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

router.get('/add', (req, res) => {
    res.render('address-book/add')
});

router.post('/add', upload.none(), (req, res) => {
    res.json(req.body)
    // res.render('address-book/add', output)
});

const getDataList = async (req) => {
    const perPage = 5;
    let page = parseInt(req.params.page) || 1;
    const output = {
        page: page,  // 目前頁數
        perPage: perPage,
        totalRows: 0,  // 總共資料筆數
        totalPages: 0,  // 總共頁數
        rows: []  // 資料內容

    }
    const [r1] = await db.query("SELECT COUNT(*) num from address_book")
    output.totalRows = r1[0].num;
    output.totalPages = Math.ceil(output.totalRows / perPage)
    if (page < 1) page = 1;
    if (output.totalPages === 0) page = 0;
    output.page = page;
    if (!output.page) {
        return output;
    }
    const sql = `SELECT * FROM address_book LIMIT ${(page - 1) * perPage},${perPage}`
    const [r2] = await db.query(sql);
    if (r2) output.rows = r2;
    for (let i of r2){
        i.birthday = moment(i.birthday).format('YYYY/MM/DD hh:mm:ss')
    }
    return output;
};

router.get('/list/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.render('address-book/list', output)
});

router.get('/list/api/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.json(output)
});

module.exports = router;

實際測試：http://localhost:3000/address-book/add

image
image1921×914 105 KB

所以現在已確定，

router.post('/add', upload.none(), (req, res) => {
    res.json(req.body)
    // res.render('address-book/add', output)
});

有送回資料，且

<script>
    function checkForm() {
        // 檢查欄位的格式
        const fd = new FormData(document.form1);
        fetch('/address-book/add',{
            method: 'POST',
            body: fd
            // 傳送的方式為mutiple form data, 不是url encoded，所以在address_book.js才要用upload.none()
        })
        .then(r=>r.json())
        .then(obj=>{
            console.log(obj);
        })
        return false;
    }
</script>

有收到資料。接下在要把資料寫入到資料庫。

把資料寫入到資料庫

可以先用phpmyadmin測試資列寫入的SQL語法，傳統的語法如下：

INSERT INTO `address_book`(`sid`, `name`, `email`, `mobile`, `birthday`, `address`, `created_at`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7])

若使用 Node MySQL 2，可以使用以下語法：

const sql = "INSERT INTO `address_book` set ?"
db.query(sql, [req.body])

實測結果

image
image1920×914 62.9 KB

address_book.js的add路由可以改寫為：

router.post('/add', upload.none(), (req, res) => {
    const output = {
        success: false
    }
    const sql = "INSERT INTO `address_book` set ?"
    db.query(sql, [req.body])
        .then(([r]) => {
            output.result = r;
            if (r.affectedRows && r.insertId) output.success = true;
            res.json(output);
        })
});

node.js的時間戳記

new Date()

address_book.js的add路由可以加上req.body.created_at = new Date();，改寫為：

router.post('/add', upload.none(), (req, res) => {
    const output = {
        success: false
    }
    const sql = "INSERT INTO `address_book` set ?"
    req.body.created_at = new Date();
    db.query(sql, [req.body])
        .then(([r]) => {
            output.result = r;
            if (r.affectedRows && r.insertId) output.success = true;
            res.json(output);
        })
});

每次執行都會把伺服器時間寫入到資料庫中。

另外，SQL語法也可以改成，同樣是把目前時間寫入到資料庫中：

const sql = "INSERT INTO `address_book` set ?, created_at=NOW()"

須注意使用 set ? 時，物件裡面不要有不該出現的MySQL欄位，如果資料表沒有那個欄位，就會出錯。

在add頁面加上訊息提示

把以下內容貼到add.ejs：

<div id="infobar" class="alert" role="alert" style="display: none;">
</div>

完整的 add.ejs 如下：

<%- include ('../parts/html-head') %>
<%- include ('../parts/navbar') %>
<div class="container">
    <div class="row">
        <div class="col-lg-6">
            <div id="infobar" class="alert" role="alert" style="display: none;">
            </div>
            <div class="card">
                <!-- <img src="..." class="card-img-top" alt="..."> -->
                <div class="card-body">
                    <h5 class="card-title">新增資料</h5>
                    <form name="form1" onsubmit="return checkForm();">
                        <div class="form-group">
                            <label for="name">name</label>
                            <input type="text" class="form-control" id="name" name="name" required>
                        </div>
                        <div class="form-group">
                            <label for="email">Email address</label>
                            <input type="email" class="form-control" id="email" name="email" required>
                        </div>
                        <div class="form-group">
                            <label for="mobile">Mobile</label>
                            <input type="text" class="form-control" id="mobile" name="mobile"
                                pattern="09\d{2}-?\d{3}-?\d{3}" required>
                        </div>
                        <div class="birthday">
                            <label for="exampleInputEmail1">Birthday</label>
                            <input type="text" class="form-control" id="birthday" name="birthday" required>
                        </div>
                        <div class="address">
                            <label for="exampleInputEmail1">Address</label>
                            <input type="text" class="form-control" id="address" name="address" required>
                        </div>

                        <button type="submit" class="btn btn-primary">Submit</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
</div>
<%- include('../parts/scripts') %>
<script>
    const infobar = $('#infobar');
    function checkForm() {
        infobar.hide();
        // 檢查欄位的格式
        const fd = new FormData(document.form1);
        fetch('/address-book/add', {
            method: 'POST',
            body: fd
            // 傳送的方式為mutiple form data, 不是url encoded，所以在address_book.js才要用upload.none()
        })
            .then(r => r.json())
            .then(obj => {
                console.log(obj);
                console.log(obj.success);
                if (obj.success) {
                    console.log('新增成功')
                    infobar
                        .removeClass('alert-danger')
                        .removeClass('alert-success')
                        .addClass('alert-success')
                        .text('新增成功');
                    setTimeout(() => {
                        infobar.slideUp();
                    }, 3000);
                } else {
                    console.log('新增失敗')
                    infobar
                        .removeClass('alert-danger')
                        .removeClass('alert-success')
                        .addClass('alert-danger')
                        .text('新增失敗');
                    setTimeout(() => {
                        infobar.slideUp();
                    }, 3000);
                }
            })
        infobar.show();
        return false;
    }
</script>
<%- include('../parts/html-foot') %>

資料刪除、資料編輯

修改list.ejs

<style>
    .table .fa-trash-alt {
        color: crimson;
    }
</style>
<%- include ('../parts/html-head') %>
<%- include ('../parts/navbar') %>
<div class="row">
    <div class="col">
        <div class="container">
            <nav aria-label="Page navigation example">
                <ul class="pagination">
                    <li class="page-item <%= (1===page)?' disabled':'' %>">
                        <a class="page-link" href="/address-book/list/<%= page-1 %>" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                    <% for( let i = 1; i <= totalPages; i++ ) { %>
                    <li class="page-item<%= (i===page)?' active':'' %>"><a class="page-link"
                            href="/address-book/list/<%= i %>"><%= i %> </a></li>
                    <% } %>
                    <li class="page-item<%= (totalPages===page)?' disabled':'' %>">
                        <a class="page-link" href="/address-book/list/<%= page+1 %>" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                </ul>
            </nav>
            <table class="table table-striped">
                <thead>
                    <tr>
                        <th scope="col"><i class="fas fa-trash-alt"></i></th>
                        <th scope="col">sid</th>
                        <th scope="col">Name</th>
                        <th scope="col">Email</th>
                        <th scope="col">Cell Phone</th>
                        <th scope="col">Birthday</th>
                        <th scope="col">Address</th>
                        <th scope="col"><i class="fas fa-edit"></i></th>
                    </tr>
                </thead>
                <tbody>
                    <% for( let i of rows) { %>
                    <tr>
                        <td>
                            <a href="/address-book/del/<%= i.sid %>"><i class="fas fa-trash-alt"></i></a>
                        </td>
                        <td><%= i.sid %> </td>
                        <td><%= i.name %> </td>
                        <td><%= i.email %> </td>
                        <td><%= i.mobile %> </td>
                        <td><%= i.birthday %> </td>
                        <td><%= i.address %> </td>
                        <td>
                            <a href="/address-book/edit/<%= i.sid %>"><i class="fas fa-edit"></i></a>
                        </td>
                    </tr>
                    <% } %>
                </tbody>
            </table>
        </div>
    </div>

</div>
<%- include('../parts/scripts') %>
<%- include('../parts/html-foot') %>

修改edit.ejs

<%- include ('../parts/html-head') %>
<%- include ('../parts/navbar') %>
<div class="container">
    <div class="row">
        <div class="col-lg-6">
            <div id="infobar" class="alert" role="alert" style="display: none;">
            </div>
            <div class="card">
                <!-- <img src="..." class="card-img-top" alt="..."> -->
                <div class="card-body">
                    <h5 class="card-title">修改資料</h5>
                    <form name="form1" onsubmit="return checkForm();">
                        <input type="hidden" name="sid" value="<%= sid %>">
                        <div class="form-group">
                            <label for="name">name</label>
                            <input type="text" class="form-control" id="name" name="name" 
                            value="<%= name %>"
                            required>
                        </div>
                        <div class="form-group">
                            <label for="email">Email address</label>
                            <input type="email" class="form-control" id="email" name="email" 
                            value="<%= email %>"
                            required>
                        </div>
                        <div class="form-group">
                            <label for="mobile">Mobile</label>
                            <input type="text" class="form-control" id="mobile" name="mobile"
                                pattern="09\d{2}-?\d{3}-?\d{3}" 
                                value="<%= mobile %>"
                                required>
                        </div>
                        <div class="birthday">
                            <label for="exampleInputEmail1">Birthday</label>
                            <input type="text" class="form-control" id="birthday" name="birthday" 
                            value="<%= birthday %>"
                            required>
                        </div>
                        <div class="address">
                            <label for="exampleInputEmail1">Address</label>
                            <input type="text" class="form-control" id="address" name="address" 
                            value="<%= address %>"
                            required>
                        </div>

                        <button type="submit" class="btn btn-primary">Submit</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
</div>
<%- include('../parts/scripts') %>
<script>
    const infobar = $('#infobar');
    function checkForm() {
        infobar.hide();
        // 檢查欄位的格式
        const fd = new FormData(document.form1);
        fetch('/address-book/edit', {
            method: 'POST',
            body: fd
            // 傳送的方式為mutiple form data, 不是url encoded，所以在address_book.js才要用upload.none()
        })
            .then(r => r.json())
            .then(obj => {
                console.log(obj);
                console.log(obj.success);
                if (obj.success) {
                    console.log('修改成功')
                    infobar
                        .removeClass('alert-danger')
                        .removeClass('alert-success')
                        .addClass('alert-success')
                        .text('修改成功');
                    setTimeout(() => {
                        infobar.slideUp();
                    }, 3000);
                } else {
                    console.log('修改失敗')
                    infobar
                        .removeClass('alert-danger')
                        .removeClass('alert-success')
                        .addClass('alert-danger')
                        .text('修改失敗');
                    setTimeout(() => {
                        infobar.slideUp();
                    }, 3000);
                }
            })
        infobar.show();
        return false;
    }
</script>
<%- include('../parts/html-foot') %>

修改address_book.js

const express = require('express');
const moment = require('moment-timezone');
const upload = require(__dirname + '/upload-module');
// 這裡不會做到檔案上傳，使用upload-module.js的multer來處理表單
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/', (req, res) => {
    // res.redirect('/address-book/list');
    res.redirect(req.baseUrl + '/list');
});

router.get('/del/:sid', (req, res) => {
    let referer = req.get('Referer')
    const sql = "DELETE FROM address_book WHERE sid=?"
    db.query(sql, [req.params.sid])
        .then(([r]) => {
            if (referer){
                res.redirect(referer)    
            }else{
                res.redirect('/address-book/list')
            }
            // if (r && r.length) {
            //     r[0].birthday = moment(r[0].birthday).format('YYYY/MM/DD hh:mm:ss');
            //     res.render('address-book/edit', r[0])
            // } else {
            //     res.redirect('/address-book/list')
            // }
        })
    // res.render('address-book/edit')
});

router.get('/edit/:sid', (req, res) => {
    const sql = "SELECT * FROM address_book WHERE sid=?"
    db.query(sql, [req.params.sid])
        .then(([r]) => {
            if (r && r.length) {
                r[0].birthday = moment(r[0].birthday).format('YYYY/MM/DD hh:mm:ss');
                res.render('address-book/edit', r[0])
            } else {
                res.redirect('/address-book/list')
            }
        })
    // res.render('address-book/edit')
});

router.post('/edit', upload.none(), (req, res) => {
    const output = {
        success: false,
        body: req.body
    }
    let sid = parseInt(req.body.sid);
    if (! sid) {
        output.error = '沒有主鍵';
        return res.json(output);
    }
    const sql = "UPDATE `address_book` SET ? WHERE sid=?";
    delete req.body.sid;
    // req.body.created_at = new Date();
    db.query(sql, [req.body, sid])
        .then(([r]) => {
            output.results = r;
            if (r.affectedRows && r.changedRows) {
                output.success = true;
            }
            res.json(output);
        })
    // res.json(output);
    // res.render('address-book/edit')
});

router.get('/add', (req, res) => {
    res.render('address-book/add')
});

router.post('/add', upload.none(), (req, res) => {
    const output = {
        success: false
    }
    // const sql = "INSERT INTO `address_book` set ?"
    const sql = "INSERT INTO `address_book` set ?, created_at=NOW()"
    // req.body.created_at = new Date();
    db.query(sql, [req.body])
        .then(([r]) => {
            output.result = r;
            if (r.affectedRows && r.insertId) output.success = true;
            res.json(output);
        })
});

const getDataList = async (req) => {
    const perPage = 5;
    let page = parseInt(req.params.page) || 1;
    const output = {
        page: page,  // 目前頁數
        perPage: perPage,
        totalRows: 0,  // 總共資料筆數
        totalPages: 0,  // 總共頁數
        rows: []  // 資料內容

    }
    const [r1] = await db.query("SELECT COUNT(*) num from address_book")
    output.totalRows = r1[0].num;
    output.totalPages = Math.ceil(output.totalRows / perPage)
    if (page < 1) page = 1;
    if (output.totalPages === 0) page = 0;
    output.page = page;
    if (!output.page) {
        return output;
    }
    const sql = `SELECT * FROM address_book ORDER BY sid desc LIMIT ${(page - 1) * perPage},${perPage}`
    const [r2] = await db.query(sql);
    if (r2) output.rows = r2;
    for (let i of r2) {
        i.birthday = moment(i.birthday).format('YYYY/MM/DD hh:mm:ss')
    }
    return output;
};

router.get('/list/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.render('address-book/list', output)
});

router.get('/list/api/:page?', async (req, res) => {
    const output = await getDataList(req);
    res.json(output)
});

module.exports = router;

刪除資料加上確認功能

方法一：confirm()

list.ejs

// 原本
<a href="/address-book/edit/<%= i.sid %>">
// 改為
<a href="javascript:deleteItem(<%= i.sid %>)">

// 加上function
<script>
    function deleteItem(sid) {
        if (confirm(`確定刪除編號為 ${sid} 的資料嗎?`)) {
            location.href = '/address-book/del/' + sid;
        }
    }
</script>

方法二：

<a href="/address-book/del/<%= i.sid %>" onclick="deleteItem(event, <%= i.sid %>)"><i class="fas fa-trash-alt"></i></a>

<script>
    function deleteItem(event, sid) {
        if (! confirm(`確定刪除編號為 ${sid} 的資料嗎?`)) {
            event.preventDefault();
        }
    }
</script>