Skip to content

Display data after user input form (mysql-nodejs)

I have a page where there is a form, in which user will fill inputs. Then, I redirect to another page in which depending on the user’s choices some data will be displayed (the data will come from a mysql database). This is my code:
index.js (here are my routes)

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

// Controllers
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' });
});

router.post('/form-submit', airTicketsController.airForm);
router.get('/air_ticketsSelect', airTicketsController.displayFlights);

module.exports = router;


airTicketsController.js (a controller where mysql queries will be executed)

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 variable1, variable2;

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

exports.displayFlights= (req, res) => {
    variable1 = req.body.from_destination;
    variable2 = req.body.to_destination
    connection.query("SELECT * FROM flight WHERE from_destination=? AND to_destination=?", [variable1, variable2], function(err, results, fields) {
        if (err) throw err;
        res.render('air_ticketsSelect', {
            title: 'flightdata',
            data: results
        });
    });
}


air_tickets.ejs (the page for the form)

<form id="form-submit" method="post" action="form-submit">
                            <div class="container" id="air-form-container">
                                <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="City or airport">
                                        </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="City or airport">
                                        </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 for displaying the data)

<table class="table table-dark table-striped">
                        <thead>
                            <tr>
                                <th>Id</th>
                                <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>

                            </tr>
                        </thead>
                        <tbody>
                            <% data.forEach(function(flight){ %>
                                <tr>
                                    <td>
                                        <%= flight.flight_id %>
                                    </td>
                                    <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>


In general, I think this is supposed to work. However, it doesn’t display the data, only an empty table. If I do the query manually (e.g. SELECT * FROM flight WHERE from_destination = ‘Paris’ AND to_destination = ‘London’), it will work correctly..
When I console.log the query it says that:
sql: ‘SELECT * FROM flight WHERE from_destination=NULL AND to_destination=NULL’, values: [ undefined, undefined ]

Answer

You are mistakenly double defining variables.

In exports.displayFlights there is no need to again initialize the variables and removing those two lines will solve your problem.

Since, there is no req.body in exports.displayFlights your variables are getting initialized to undefined.