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

Question

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)


                            
                                
                                    
                                        
                                            
                                            Roundtrip
                                        
                                            
                                            One way
                                        
                                        
                                    
                                
                                
                                    
                                        
                                            From: 
                                            

                                            
                                        
                                    
                                    
                                        
                                            To: 
                                            

                                            
                                        
                                    
                                
                                
                                    
                                        
                                            Depart date: 
                                            
                                        
                                    
                                    
                                        
                                            Return date:

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


                
                     DEPARTURE
                    

                    
                        
                                <% departFlights.forEach(function(flight){ %>
                                    
                                    <% }); %>
                            
                            
                                
                                    Airline
                                    From
                                    To
                                    Depart date
                                    Arrival date
                                    Depart time
                                    Arrival time
                                    Price
                                    Action
                                
                            
                            
                                        
                                            <%= flight.airline %>
                                        
                                        
                                            <%= flight.from_destination %>
                                        
                                        
                                            <%= flight.to_destination %>
                                        
                                        
                                            <%= flight.depart_date.toLocaleDateString('el-GR') %>
                                        
                                        
                                            <%= flight.arrival_date.toLocaleDateString('el-GR') %>
                                        
                                        
                                            <%= flight.depart_time %>
                                        
                                        
                                            <%= flight.arrival_time %>
                                        
                                        
                                            <%= flight.flight_price + ' €' %>

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:

user_2851502 · Answer

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.

user_13087086 · Answer

Create another variable var3 and in your first query initialize it.

var var3;

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;

        var3 = results;

        res.render('air_ticketsSelect', {
            title: 'Flights',
            departFlights: results
        });
    });
}

This way you will not have to query your database again and again. You can simply pass var3 exactly as you are passing result of your query. LIKE

exports.saveDepartureFlightForm = (req, res) => {
    
        res.render('air_ticketsSelectReturn', {
            title: 'Flights',
            departFlightId: var3.flight_id
        });
        console.log(departFlightId);
    });
}

EDIT:

you have three problems in your code

router.post('/form-submit', airTicketsController.airForm); should be changed to

router.post('/form-submit', function(req,res,next){
    airTicketsController.airForm(req,res);
}

and similarly for others.

you are importing the object exported by the js file instead of the js file (which was correct in the your previous question I answered where you were querying database in index.js) So, change require('../controllers/airTicketsController') to require('../controllers/airTicketsController.js')
You should remove for now router.post('/saveDepartureFlightForm', airTicketsController.saveDepartureFlightForm); and its function. They may even create errors. Better to solve the problem between two pages rather than introducing a third page. Beacuse I don't think the href option is correct in the button tag.

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

2 Answers