How can I create a text input with autocomplete and then display data from mysql database in express?

Admin 1
Viewed 424

So, I have a form, in which the user will select inputs. Firstly, I want to create a text input and in this when the user writes for example A, it will suggest every data from the MYSQL database that starts with A etc. The page of the form is /air_tickets and then the user will be redirected to another page.
File structure:
enter image description here
Inside the views folder, there are all my .ejs pages (html).
air_tickets.ejs

<form id="form-submit" method="post">
                            <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 typeahead tt-query" spellcheck="false" autocomplete="off" placeholder="City or airport" 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="City or airport">
                                        </div>
                                    </div>
                                   <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>

I also found a way with typeahead library but it doesn't work properly. This is the script (I have it inside the air_tickets.ejs file):

<script>
        $(document).ready(function() {
            $('input.typeahead').typeahead({
                name: 'typeahead',
                remote: 'http://localhost:3000/search?key=%QUERY',
                limit: 10
            });
        });
    </script>


index.js (In a routes file)

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

router.get('/search', airTicketsController.fromDestinations);


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

//---------------
// autocomplete for from_destination
exports.fromDestinations = (req, res) => {
    connection.query('SELECT from_destination FROM flight where from_destination LIKE "%' + req.query.key + '%"',
        function(err, rows, fields) {
            if (err) throw err;
            var data = [];
            for (i = 0; i < rows.length; i++) {
                data.push(rows[i].from_destination);
            }
            res.end(JSON.stringify(data));
        });
}


app.js

const path = require('path');
const express = require('express');
const ejs = require('ejs');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const app = express();

// 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! (app)');
});

//set views file
app.set('views', path.join(__dirname, 'views'));

//set public file
app.use(express.static(__dirname + '/public'));

//set view engine
app.set('view engine', 'ejs');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

const routes = require('./server/routes/index');

app.use('/', routes);

// Server Listening
app.listen(3000, () => {
    console.log('Server is running at port 3000');
});

All the data will come from a MYSQL table called flight and have columns like from_destination and to_destination
I can't include the typeahead library correctly. But if you know some other way to create the auto-correct text depending on my SQL query, please suggest it to me.

express ejs node.js mysql javascript
2 Answers

I found this about jQuery autocomplete:

<script>
        $(function() {
            var availableTags = [
                "ActionScript",
                "AppleScript",
                "Asp",
                "BASIC",
                "C",
                "C++",
                "Clojure",
                "COBOL",
                "ColdFusion",
                "Erlang",
                "Fortran",
                "Groovy",
                "Haskell",
                "Java",
                "JavaScript",
                "Lisp",
                "Perl",
                "PHP",
                "Python",
                "Ruby",
                "Scala",
                "Scheme"
            ];
            $("#tags").autocomplete({
                source: availableTags
            });
        });
    </script>


where tags is the id of the text input. And it works fine.
Although, my problem now is how I will take the data from the database

Admin1

First of all you should edit question title as the original problem has progressed and you should ask that in another question.

For Although, my problem now is how I will take the data from the database part

  1. instead of form id="form-submit", it would be form action="form-submit"
  2. place a button or input(with type=submit) inside the form.
  3. edit your app.js's bodyParser lines to
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
  1. add module.exports = app; at end of app.js and add module.exports = connection; at end of airTicketsController.js (also remove the autofill codes from airTicketsController.js,ejs form and index.js for now to avoid unwanted interference)
  2. then you have to read the form by adding this
var variable1, variable2;
router.post('/form-submit', function(req,res,next){
    variable1 = req.body.from_destination;
    variable2 = req.body.to_destination
    res.redirect('/yournextpage');
});
  1. then query your database according to your needs using these two variables on the newpage
router.get('/yournextpage', function(req,res, next){
    airTicketsController.query("SELECT * FROM flight WHERE your_column1=? AND your_column2=?",[variable1,variable2],function(err, results, fields){
      res.render('yournextpage', {title: 'flightdata', retrieved: results});
    });
});

module.exports = router;
user_130870860
Related