mysql - Nodejs HTML 选择列表

Question

我是nodejs和mysql的新手。我在生成包含存储在 MySQL 中的数据的列表时遇到问题。其他一切工作正常，我希望从 MYSQL“测试”表中的任何内容生成“国家/地区”的选择下拉列表。据我所知，将来我会添加更多选择列表以及从 MYSQL 生成的更多数据。我不确定要编写什么代码来生成循环以从 http://localhost:7002/getJson 读取数据，或者这甚至是解决问题的方法。任何帮助将不胜感激!谢谢

路线.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: 'test'
 });

 connection.connect(); // <---- AND HERE

 // all environments
 app.set('port', process.env.PORT || 7002);


 app.get('/getJson',function(req,res){
 connection.query('SELECT * FROM Country', function(err, rows, fields){
    if(err) {
        console.log(err); 
        res.json({"error":true});
    }
    else { 
        console.log(result); 
        res.json(result); 
    }
        console.log('Connection result error '+err);
            res.writeHead(200, { 'Content-Type': 'application/json'});
        res.end(JSON.stringify(rows));

    });

 } );

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

服务器.js

var express = require('express');
var session = require('express-session');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var morgan = require('morgan');
var app = express();
var port = process.env.PORT || 8080;

var passport = require('passport');
var flash = require('connect-flash');

require('./config/passport')(passport);

app.use(morgan('dev'));
app.use(cookieParser());
app.use(bodyParser.urlencoded({
 extended: true
}));

app.set('view engine', 'ejs');

app.use(session({
 secret: 'justasecret',
 resave:true,
 saveUninitialized: true
}));

app.use(passport.initialize());
app.use(passport.session());
app.use(flash());


require('./app/routes.js')(app, passport);

app.listen(port);
console.log("Port: " + port);

护照.js

var LocalStrategy = require("passport-local").Strategy;

var mysql = require('mysql');
var bcrypt = require('bcrypt-nodejs');
var dbconfig = require('./database');
var connection = mysql.createConnection(dbconfig.connection);

connection.query('USE ' + dbconfig.database);

module.exports = function(passport) {
 passport.serializeUser(function(user, done){
  done(null, user.id);
 });

 passport.deserializeUser(function(id, done){
  connection.query("SELECT * FROM users WHERE id = ? ", [id],
   function(err, rows){
    done(err, rows[0]);
   });
 });

 passport.use(
  'local-signup',
  new LocalStrategy({
   usernameField : 'username',
   passwordField: 'password',
   passReqToCallback: true
  },
  function(req, username, password, done){
    var fruit= req.body.fruit;
    var PhoneNumber= req.body.PhoneNumber;
    var Country= req.body.Country;
    var newUserMysql = {
          username: username,
          password: password,
          fruit: fruit,
          PhoneNumber: PhoneNumber,
          Country: Country,
         };

         var insertQuery = "INSERT INTO users (username, password, fruit, PhoneNumber, Country) values (?, ?, ?, ?, ?)";

         connection.query(insertQuery, [newUserMysql.username,newUserMysql.password, newUserMysql.closureplan,newUserMysql.UIR,newUserMysql.CSJ],
          function(err, rows){
           newUserMysql.id = rows.insertId;

           return done(null, newUserMysql);
          });

  })
 );

 passport.use(
  'local-login',
  new LocalStrategy({
   usernameField : 'username',
   passwordField: 'password',
   passReqToCallback: true
  },
  function(req, username, password, done){
   connection.query("SELECT * FROM users WHERE username = ? ", [username],
   function(err, rows){
    if(err)
     return done(err);
    if(!rows.length){
     return done(null, false, req.flash('loginMessage', 'No User Found'));
    }
    if(!bcrypt.compareSync(password, rows[0].password))
     return done(null, false, req.flash('loginMessage', 'Wrong Password'));

    return done(null, rows[0]);
   });
  })
 );
};

signup.js

<!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">
    <div class="form-group">
     <label>Username</label>
     <input type="text" class="form-control" name="username">
    </div>

    <div class="form-group">
     <label>Password</label>
     <input type="text" class="form-control" name="password">
    </div>

    <div class="form-group">
        <label>Select Fruit:</label>
            <select name="fruit" type="form" class="form-control">
              <option value="Apple">Apple</option>
              <option value="Orange">Orange</option>
              <option value="Banana">Banana</option>
            </select><br />


    </div>

    <div class="form-group">
        <label>Phone Number#</label>
        <input name="PhoneNumber" type="textbox" class="form-control" >
    </div>

    <div class="form-group">
        <label>Country</label>
        <select name="Country" type="form" class="custom-select">

       </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>

数据库.js

module.exports = {
 'connection':{
  'host':'localhost',
  'user':'root',
  'password':'password'
 },
 'database':'signup',
 'user_table':'information',

}

Answer 1

在你的前端代码中你得到 /getJSON它返回 JSON目的。然后您需要将元素附加到您的 <select>标记为<option>

fetch('https://jsonplaceholder.typicode.com/todos')
  .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);
    });
    
  })
  

<select id="test">
</select>