Recommand · June 1, 2021 0

How to store selected database column as a variable from html page (nodejs – express)?

I have a page where I display flights from my mysql database.Firstly, I have a form where the user selects destinations and dates for the flight, and then after submitting the form I go to another page where the flights are displayed depending on the user’s choices. At each flight there is a Book button, which redirects the user at another page. When the user clicks this button (and so selects a flight), I want to take the id of the selected flight from my database, store it maybe at a variable (or something) in order to know which flight the user selected. Here is my code:

index.js (here are the routes)

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('/saveDepartureFlightForm', airTicketsController.saveDepartureFlightForm);
router.get('/air_ticketsSelectReturn', airTicketsController.displayFlightsForReturn);

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('Database Connected!');
});

//---------------
var fromDestination, toDestination, departDate, returnDate;
var departFlightId, returnFlightId;

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

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

exports.saveDepartureFlightForm = (req, res) => {
    let query = connection.query({ sql: "SELECT * FROM flight WHERE from_destination=? AND to_destination=? AND depart_date=?", values: [fromDestination, toDestination, departDate] }, function(err, results, fields) {
        if (err) throw err;
        res.render('air_ticketsSelectReturn', {
            title: 'Flights',
            departFlightId: results.flight_id
        });
        console.log(departFlightId);
    });
}

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

air_tickets.ejs (the first form)

<form id="form-submit" method="POST" action="/form-submit">
                            <div class="container" id="air-form-container">
                                <div class="row">
                                    <div class="col-md-12">
                                        <div class="form-check-inline">
                                            <label class="form-check-label" for="roundtrip" id="myradio">
                                            <input type="radio" class="form-check-input" id="radio1" name="myradio" value="roundtrip" autocomplete="on">Roundtrip
                                        </label>
                                            <label class="form-check-label" for="oneway" id="myradio">
                                            <input type="radio" class="form-check-input" id="radio2" name="myradio" value="oneway" autocomplete="off">One way
                                        </label>
                                        </div>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label for="from_destination">From: </label>
                                            <br>
                                            <input type="text" name="from_destination" class="form-control" placeholder="Enter city" required>
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label for="to_destination">To: </label>
                                            <br>
                                            <input type="text" name="to_destination" class="form-control" placeholder="Enter city" required>
                                        </div>
                                    </div>
                                </div>
                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label for="depart_date">Depart date: </label>
                                            <input type="date" name="depart_date" class="form-control" placeholder="Enter depart date" required>
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label for="return_date">Return date: </label>
                                            <input type="date" name="return_date" class="form-control" placeholder="Enter return date">
                                        </div>
                                    </div>
                                </div>
                                <br>
                                <div class="row">
                                    <div class="col-md-12">
                                        <div class="form-btn">
                                            <button type="submit" class="btn btn-primary" id="submit-btn">Search flights 
                                            <i class="fas fa-search" aria-hidden="true"></i>
                                        </button>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </form>

air_ticketsSelect.ejs (the page where the flights are displayed)

<!-- Display Flights -->
                <div class="col-md-9" id="jumboCopy">
                    <h1> DEPARTURE</h1>
                    <br>
                    <form id="departureFlightForm" method="POST" action="/saveDepartureFlightForm">
                        <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>
                                <% departFlights.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>
                                        <td>
                                            <!-- <a href="air_ticketsSelectReturn" type="button" class="btn btn-light btn-small"></i>Book</a> -->
                                            <button type="submit" href="air_ticketsSelectReturn" class="btn btn-light btn-small">Book 
                                        </td>
                                    </tr>
                                    <% }); %>
                            </tbody>
                        </table>    
                    </form>
                </div>

When the user clicks at the button Book the user is redirected to the air_ticketsSelectReturn, which is a similar page for returns. I want to store the flight_id at a variable so I can use it later.
Furthermore, this is the mysql table for the flights:
enter image description here

You can make use of sessions
http://expressjs.com/en/resources/middleware/session.html

Maintain a session for the current user based on the requests and actions.