javascript - 如何通过nodejs在html页面添加过滤器（使用mysql数据库中的数据）？

Question

我通过 html (ejs) 在表中显示来自 mysql 数据库的数据。我有一些过滤器，并根据它们执行查询并显示合适的数据。这是我的代码：
air_ticketsSelect.ejs

<div class="container-fluid px-5">
            <div class="row">
                <div class="col-md-3">
                    <div class="jumbotron">
                        <form action="/filterFlights/" method="POST" id="airFilters">
                            <div class="row">
                                <div class="col-md-12">
                                    <div class="form-group">
                                        <label for="minPriceFilter">Min price: </label>
                                        <input type="text" class="form-control" name="minPriceFilter" placeholder="Enter min price">
                                    </div>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col-md-12">
                                    <div class="form-group">
                                        <label for="maxPriceFilter">Max price: </label>
                                        <input type="text" class="form-control" name="maxPriceFilter" placeholder="Enter max price">
                                    </div>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col-md-12">
                                    <div class="form-group">
                                        <label for="airlineFilter">Airline:</label>
                                        <select class="form-control" name="airlineFilter">
                                            <option disabled selected value> -- select airline -- </option>
                                            <option>Airline1</option>
                                            <option>Airline2</option>
                                            <option>Airline3</option>
                                            <option>Airline4</option>
                                        </select>
                                    </div>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col-md-12">
                                    <div class="form-check-inline">
                                        <label name="resetFilters" class="form-check-label">
                                          <input type="checkbox" class="form-check-input" name="resetFilters">Reset filters
                                        </label>
                                    </div>
                                </div>
                            </div>
                            <br>
                            <div class="row">
                                <div class="col-md-12">
                                    <input type="submit" class="form-control btn btn-primary" name="submit" placeholder="Filter">
                                </div>
                            </div>
                        </form>
                    </div>
                </div>
                <!-- Display Flights -->
                <div class="col-md-9" id="jumboCopy">
                    <br>
                    <table class="table table-dark table-striped">
                        <thead>
                            <tr>
                                <th>Airline</th>
                                <th>From</th>
                                <th>To</th>
                                <th>Depart date</th>
                                <th>Arrival date</th>
                                <th>Depart time</th>
                                <th>Arrival time</th>
                                <th>Price</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tbody>
                            <% data.forEach(function(flight){ %>
                                <tr>
                                    <td>
                                        <%= flight.airline %>
                                    </td>
                                    <td>
                                        <%= flight.from_destination %>
                                    </td>
                                    <td>
                                        <%= flight.to_destination %>
                                    </td>
                                    <td>
                                        <%= flight.depart_date.toLocaleDateString('el-GR') %>
                                    </td>
                                    <td>
                                        <%= flight.arrival_date.toLocaleDateString('el-GR') %>
                                    </td>
                                    <td>
                                        <%= flight.depart_time %>
                                    </td>
                                    <td>
                                        <%= flight.arrival_time %>
                                    </td>
                                    <td>
                                        <%= flight.flight_price + ' €' %>
                                    </td>
                                </tr>
                                <% }); %>
                        </tbody>
                    </table>

                </div>
            </div>

airTicketsController.js

const mysql = require('mysql');

// DB connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'myuser',
    password: 'mypassword',
    database: 'mydatabase'
});

connection.connect(function(error) {
    if (!!error) console.log(error);
    else console.log('CONGRATS! Database Connected! (airTicketsController)');
});

var fromDestination, toDestination;

exports.airForm = (req, res) => {
    fromDestination = req.body.from_destination;
    toDestination = req.body.to_destination
    res.redirect('/air_ticketsSelect');
}

exports.displayFlights = (req, res, next) => {
    let query = connection.query({ sql: "SELECT * FROM flight WHERE from_destination=? AND to_destination=?", values: [fromDestination, toDestination] }, function(err, results, fields) {
        if (err) throw err;
        res.render('air_ticketsSelect', {
            title: 'Flights',
            data: results
        });
    });
}

exports.filterFlights = (req, res, next) => {
    var minPriceFilter = req.body.minPriceFilter;
    var maxPriceFilter = req.body.maxPriceFilter;
    var airlineFilter = req.body.airlineFilter;
    var resetFilters = req.body.resetFilters;

    if (minPriceFilter != '' && maxPriceFilter != '' && airlineFilter != '' && resetFilters == '') {
        let query = connection.query({ sql: "SELECT * FROM flight WHERE (flight_price BETWEEN ? AND ?) AND airline=? AND from_destination=? AND to_destination=?", values: [minPriceFilter, maxPriceFilter, airlineFilter, fromDestination, toDestination] }, function(err, results, fields) {
            if (err) throw err;
            res.render('air_ticketsSelect', {
                title: 'Flights',
                data: results
            });
        });
    }
}

index.js

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

// Air tickets controller
const airTicketsController = require('../controllers/airTicketsController');

/* GET home page. */
router.get('/', function(req, res, next) {
    res.render('home', { title: 'Express' });
});

// Air tickets page
router.get('/air_tickets', function(req, res, next) {
    res.render('air_tickets', { title: 'Air tickets' });
});
// Submit air form and search for flights
router.post('/form-submit', airTicketsController.airForm);
router.get('/air_ticketsSelect', airTicketsController.displayFlights);

router.post('/filterFlights', airTicketsController.filterFlights);

module.exports = router;

我还没有完成上面的 if 部分，但我认为它应该这样工作。逻辑是有许多 if (else if) 子句并在每个子句中执行不同的查询。

标签： javascriptmysqlnode.jsexpressejs