I need some assistance in passing the MYSQL query results to an HTML select list. I was able to pass some data from a JSON list from here - http://jsonplaceholder.typicode.com/todos, but am unable to pass my own data that is sent to localhost:7002/getJson. Is it a formatting thing, please take a look at my code and data and see what can be changed. Thanks!
route.js
module.exports = function(app, passport) {
app.get('/', function(req, res){
res.render('index.ejs');
});
app.get('/login', function(req, res){
res.render('login.ejs', {message:req.flash('loginMessage')});
});
app.post('/login', passport.authenticate('local-login', {
successRedirect: '/profile',
failureRedirect: '/login',
failureFlash: true
}),
function(req, res){
if(req.body.remember){
req.session.cookie.maxAge = 1000 * 60 * 3;
}else{
req.session.cookie.expires = false;
}
res.redirect('/');
});
app.get('/signup', function(req, res){
res.render('signup.ejs', {message: req.flash('signupMessage')});
});
app.post('/signup', passport.authenticate('local-signup', {
successRedirect: '/profile',
failureRedirect: '/signup',
failureFlash: true
}));
app.get('/profile', isLoggedIn, function(req, res){
res.render('profile.ejs', {
user:req.user
});
});
app.get('/logout', function(req,res){
req.logout();
res.redirect('/');
})
//-SQL QUERY
var express = require('express')
, http = require('http')
, mysql = require('mysql'); // <---- HERE
var app = express();
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: "password",
database: 'testdb'
});
connection.connect(); // <---- AND HERE
// all environments
app.set('port', process.env.PORT || 7002);
app.get('/getJson',function(req,res){
connection.query('SELECT * FROM testtable', function(err, result, fields){
if(err) {
console.log(err);
res.json({"error":true});
}
else {
// console.log(result);
console.log(JSON.stringify(result));
res.json(result);
}
});
} );
http.createServer(app).listen(app.get('port'), function(){
console.log('Express server listening on port ' + app.get('port'));
});
};
//-SQL QUERY END
function isLoggedIn(req, res, next){
if(req.isAuthenticated())
return next();
res.redirect('/');
}
signup.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Login Register</title>
<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.0.2/css/bootstrap.min.css">
<link rel="stylesheet"
href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.min.css">
<style>
html{
padding:50px;
}
</style>
</head>
<body>
<div class="container">
<div class="col-sm-6 col-sm-offset-3">
<h2>Register</h2>
<% if(message.length > 0) { %>
<div class="alert alert-danger"><%= message %></div>
<% } %>
<form action="/signup" method="post">
<script>
fetch('http://localhost:7002/getJson')
.then(response => response.json())
.then(json => {
console.log(json);
let select = document.getElementById("test");
json.forEach(e=>{
var opt1 = document.createElement("option");
opt1.text = e.title;
opt1.value = e.id;
select.add(opt1);
});
})</script>
<script>
fetch('https://jsonplaceholder.typicode.com/todos')
.then(response => response.json())
.then(json => {
// console.log(json);
let select = document.getElementById("hi");
json.forEach(e=>{
var opt1 = document.createElement("option");
opt1.text = e.title;
// opt1.value = e.id;
select.add(opt1);
});
})</script>
<div class="form-group">
<select id="test">
</select><br>
<select id="hi">
</select><br>
</div>
<button type="submit" class="btn btn-succcess btn-lg">Register</button>
</form>
<hr>
<p>Need an account <a href="/signup">Register</a></p>
<p>Go Back <a href="/">Home</a>.</p>
</div>
</div>
</body>
</html>
from: http://localhost:7002/getJson
from: http://localhost:8080/signup
from console
<script>fetch statement you need to changehttps://jsonplaceholder.typicode.com/todostohttp://localhost:7002/getJsonand I would un-comment// opt1.value = e.id;